How To Calculate Last Month Sales In Power Bi

How to Calculate Last Month Sales in Power BI

In Power BI, you can calculate the last month sales using various DAX functions. Here’s how you can do it:

1. Assuming you have a table or query result containing the sales data, create a new calculated column to extract the month and year from the date column.

    SalesDataTable:
    +-----------+-------------+-------+
    |  Date     |  Sales      | Month |
    +-----------+-------------+-------+
    | 2022-01-01| 1000        | Jan   |
    | 2022-02-01| 1500        | Feb   |
    | 2022-03-01| 2000        | Mar   |
    | ...       | ...         | ...   |
    +-----------+-------------+-------+
    

2. Create a new measure to calculate the total sales of the last month.

    LastMonthSales = CALCULATE(SUM(SalesDataTable[Sales]), DATEADD(SalesDataTable[Date], -1, MONTH))
    

3. Use the LastMonthSales measure in your Power BI visuals or tables to display the calculated result.

    Visuals:
    +---------------------+
    | Last Month Sales     |
    +---------------------+
    | $2,000               |
    +---------------------+
    

Explanation:

• We first need to extract the month and year from the date column. This can be done using the following DAX formula:

    Month = FORMAT(SalesDataTable[Date], "MMM")
    

• Then, we create a measure using the CALCULATE function to sum the sales for the previous month. The DATEADD function is used to go back by 1 month in the date column.
• Finally, we can use the LastMonthSales measure in Power BI visuals or tables to display the calculated result.

Example:

Let’s consider the sales data from the SalesDataTable as shown above. Using the provided DAX formulas, the LastMonthSales measure will calculate the sum of sales for the previous month (February 2022). The result will be $2,000.