In Power BI, using DAX (Data Analysis Expressions) to calculate Previous Month and Previous Year Sales is a common requirement for time-based comparisons. Here’s how you can create these calculations, using the sample data structure described earlier.
Sample Data Structure
Assume we have the following columns in our Sales table:
OrderDate: The date when the order was placed.SalesAmount: The amount of sales for the order.
We also assume a DateTable which includes the necessary columns for date intelligence calculations (e.g., Date, Year, Month, MonthName, Quarter, etc.).
Previous Month Sales
To calculate the previous month’s sales, you can use the PREVIOUSMONTH function. This function returns a table containing all the dates from the previous month, based on the current context.
Previous Month Sales Measure
DAX
PreviousMonthSales =
CALCULATE(
SUM(Sales[SalesAmount]),
PREVIOUSMONTH(DateTable[Date])
)
In this measure:
SUM(Sales[SalesAmount])sums theSalesAmountcolumn.PREVIOUSMONTH(DateTable[Date])adjusts the filter context to include only dates from the previous month.
Previous Year Sales
To calculate the previous year’s sales, you can use the PREVIOUSYEAR function. This function returns a table containing all the dates from the previous year, based on the current context.
Previous Year Sales Measure
DAX
PreviousYearSales =
CALCULATE(
SUM(Sales[SalesAmount]),
PREVIOUSYEAR(DateTable[Date])
)
In this measure:
SUM(Sales[SalesAmount])sums theSalesAmountcolumn.PREVIOUSYEAR(DateTable[Date])adjusts the filter context to include only dates from the previous year.
Example Usage in a Report
- Load Data: Ensure your data (both
SalesandDateTable) is loaded into Power BI. - Create Date Table: If you don’t have a
DateTable, you can create one using:DAX
DateTable = CALENDAR(MIN(Sales[OrderDate]), MAX(Sales[OrderDate]))- Establish Relationships: Create a relationship between the
OrderDatecolumn in theSalestable and theDatecolumn in theDateTable. - Create Measures: Add the
PreviousMonthSalesandPreviousYearSalesmeasures using the DAX expressions provided. - Add to Report: Drag and drop the new measures into your report visualizations (e.g., tables, bar charts) to compare sales data with previous month and previous year.
Visualization Example
- Table: Create a table with columns
OrderDate,SalesAmount,PreviousMonthSales, andPreviousYearSalesto compare current sales with the previous month and previous year. - Line Chart: Plot
OrderDateon the x-axis and theSalesAmount,PreviousMonthSales, andPreviousYearSalesmeasures on the y-axis to visualize trends over time.
Sample Data and Visualization
To demonstrate, let’s assume we have the following sample data in our Sales table:
| OrderID | OrderDate | SalesAmount |
|---|---|---|
| 1 | 2023-01-15 | 150.00 |
| 2 | 2023-02-20 | 250.00 |
| 3 | 2023-03-25 | 300.00 |
| 4 | 2023-04-10 | 100.00 |
| 5 | 2023-05-05 | 400.00 |
| 6 | 2023-06-15 | 350.00 |
| 7 | 2023-07-20 | 450.00 |
| 8 | 2023-08-25 | 200.00 |
| 9 | 2023-09-10 | 500.00 |
| 10 | 2023-10-15 | 600.00 |
| 11 | 2024-01-10 | 700.00 |
| 12 | 2024-02-15 | 800.00 |
| 13 | 2024-03-20 | 900.00 |
| 14 | 2024-04-25 | 1000.00 |
| 15 | 2024-05-01 | 1100.00 |
| 16 | 2024-05-05 | 1200.00 |
| 17 | 2024-05-10 | 1300.00 |
| 18 | 2024-05-15 | 1400.00 |
| 19 | 2024-05-20 | 1500.00 |
| 20 | 2024-06-01 | 1600.00 |
| 21 | 2024-06-05 | 1700.00 |
| 22 | 2024-07-10 | 1800.00 |
| 23 | 2024-08-15 | 1900.00 |
| 24 | 2024-09-20 | 2000.00 |
| 25 | 2024-10-25 | 2100.00 |
| 26 | 2024-11-01 | 2200.00 |
| 27 | 2024-12-05 | 2300.00 |
| 28 | 2024-12-10 | 2400.00 |
| 29 | 2024-12-15 | 2500.00 |
| 30 | 2024-12-20 | 2600.00 |
In this scenario, the PreviousMonthSales and PreviousYearSales measures will dynamically calculate the sales for the previous month and previous year based on the current filter context in your reports. This allows you to make time-based comparisons and identify trends in your sales data effectively.
