Creating Power BI DAX Measures for Current and Previous Quarter Sales
Previous Quarter =
CALCULATE ( [Total Sales], PREVIOUSQUARTER ( Date[Date] ) )
To create a measure that displays the sales from the previous quarter, you can use the PREVIOUSQUARTER function in DAX. This function returns a table that contains all the dates from the previous quarter, based on the current context of the Date column.
Example Scenario
Given the following meta data:
| Table Name | Column Name | Description | Data Type |
| Date | Date | Calendar date | Date |
| Date | Month | Calendar month | Text |
| Date | Year | Calendar year | Integer |
| Sales | Sale | Sales value | Decimal number |
| Sales | Date | Calendar date | Date |
The Date table relates to the Sales table by using the Date columns.
The model contains the following DAX measure, Total Sales = SUM(Sales[Sale])
Let’s say we need to create another measure named Previous Quarter to display the sales one quarter before the selected period.
Apply the above DAX Expression:
Previous Quarter =
CALCULATE ( [Total Sales], PREVIOUSQUARTER ( Date[Date] ) )
Explanation
• CALCULATE: This function modifies the context in which the data is evaluated.
• [Total Sales]: This is the measure you already have, which sums up the sales values.
• PREVIOUSQUARTER(Date[Date]): This function returns a table with all the dates from the previous quarter based on the current context of the Date column.
This measure will calculate the total sales for the quarter immediately preceding the selected period.
End result
Let's assume we have the following sales data:
| Date | Sales |
| 2024-01-15 | 100 |
| 2024-02-15 | 150 |
| 2024-03-15 | 200 |
| 2024-04-15 | 250 |
| 2024-05-15 | 300 |
| 2024-06-15 | 350 |
| 2024-07-15 | 400 |
| 2024-08-15 | 450 |
| 2024-09-15 | 500 |
If the current selected period is Q3 2024 (July to September), the Previous Quarter measure would calculate the total sales for Q2 2024 (April to June).
Here's how the calculation would look:
• Q2 2024 Sales: 250 (April) + 300 (May) + 350 (June) = 900
So, the result of the Previous Quarter measure for Q3 2024 would be 900.