DAX year over year comparison

Question

In a report that I am making in DAX I would like to have year over year comparison of a measure defined as:

Sales Count := CALCULATE(COUNT(fData[PN]);fData[Proc.Type]="CU")

This measure basically counts the amount of sales that were generated. I have a dataset that spans from the beginning of 2014 up to today (16/01/2018). My calendar table consists of a date column, a year column and a month column and is ranges from 01/01/2014 up to 31/12/2018 (to avoid contiguous date errors when using time intelligence functions).

I have defined a measure called PY Sales count as:

PY Sales Count := CALCULATE([Sales Count];SAMEPERIODLASTYEAR(dCalendar[Dates]))

This measure does the job I want it to do, aside from one issue. When comparing the month of january of this year to the month of January to last year, it appears that we are doing terrible. This is because the SAMEPERIODLASTYEAR function considers the FULL month of January for the year 2017 and compares it to the period from the first of January until the 16th of January (today).

I think I should thus try to build a custom measure to 'filter' the date table such that when the year over year measure considers the running month, it only takes the same period as the one on which sales were recorded to compare against, not the full period...

However, due to my sparse DAX experience, I cannot seem to get this right...

If anyone could help/assist me on this, that would be hugely appreciated!

Thanks in advance

This is interesting - I guess the filter context for that row is January-2018 so the calculation is using that so find the equivalent last year: I'll be interested to see the solution to this. — whytheq

Alexis Olson Alexis Olson · Accepted Answer · 2018-01-16T17:25:13

I haven't had a chance to test this, but try something along these lines:

PY Sales Count := CALCULATE([Sales Count]; 
                      FILTER(dCalendar;
                          dCalendar[Dates] <= DATEADD(TODAY(), -1, year);
                          SAMEPERIODLASTYEAR(dCalendar[Dates])))

DAX year over year comparison

1 Answers