If it is necessary to compare one month against the same month in the previous year, this calculation provides a good starting point:
Previous Year = CALCULATE ( [Sales Amount], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
With this measure, a report can show current year sales against previous year sales:
Unfortunately, the calculation is not perfect. At the year level, it compares the full previous year against an incomplete current year – in this example there are no sales after September 5th in the current year.
Besides, the problem appears not only at the year level, but also at the month level. Indeed, in September the Previous Year measure returns sales for the entire month of September in the previous year. The comparison is unfair, as there are only five days’ worth of sales in September of the current year.
A possible solution is to create a calculated column in the Date table in order to remove dates in the past that should be ignored. If the last date in the fact table is September 5th for the current year, then all the dates after September 5th in previous years can be marked to avoid considering them in the calculation.
The code for that calculated column is:
IsPast = VAR LastSaleDate = MAX ( Sales[Order Date] ) VAR LastSaleDatePY = EDATE ( LastSaleDate, -12 ) RETURN 'Date'[Date] <= LastSaleDatePY
This code stores the last date of sales into LastSaleDate, then it moves it back one year (twelve months) using the EDATE function. Finally, it checks whether the current date is earlier than the last date in the previous year. The next figure shows that IsPast calculated column, which changes value on September 6th, 2008 (in the demo model, the last year is 2009):
Once the column is in place, the Adjusted Previous Year measure can compute a variation of the Previous Year calculation. The variation specifies that it only considers dates in the previous year for which the IsPast column is True:
Adjusted Previous Year = CALCULATE( [Sales Amount], SAMEPERIODLASTYEAR ( 'Date'[Date] ), 'Date'[IsPast] = TRUE )
The next figure shows the result – it provides a fairer comparison between current and previous years:
Articles in the DAX 101 series
- Mark as Date table (Jul 20, 2020)
- Using USERELATIONSHIP in DAX (May 18, 2020)
- Sorting months in fiscal calendars (Nov 25, 2019)
- Previous year up to a certain date (Sep 12, 2019)
- Using CONCATENATEX in measures (Jun 24, 2019)
- Automatic time intelligence in Power BI (May 13, 2019)
- Creating a simple date table in DAX (May 11, 2019)
- Year-to-date filtering weekdays in DAX (Apr 29, 2019)
- Summing values for the total (Apr 15, 2019)
- Counting working days in DAX (Mar 13, 2019)
- Computing running totals in DAX (Mar 4, 2019)