Partager via

Year over Year in DAX - SAMEPERIODLASTYEAR needs a filter scope

  • Article

This week I'm working to create a measure to count the total commits in a repository. I thought it was working until I looked at the grand total and found something was off.   

For the case of Grand Total, the value was mysterious. These were the measures I was using. 

Total commits YoY% old:=DIVIDE([Total commits]-[Total Commits Same Period Last year],[Total Commits Same Period Last year])

Total commits same period last year:=CALCULATE([Total commits], SAMEPERIODLASTYEAR(DateTable[DATE]))

 

I looked into the reason for this. It's because the Grand Total case does not include any filters. When this happens, [Total commits] gets calculated for "All time" which works out to 2015 and earlier. Then SAMEPERIODLASTYEAR is calculated for "All time minus the last year" so 2014 and earlier. Yikes.

 

The solution I came up with was to implement a 12 month rolling window for the no filters case. This will give a good YoY comparison if no filters is provided, and will respect the filtered date range otherwise.

Two things were required. First, I added a column to my DateTable called "isLast12Months" as below:

=if(

OR(

AND( [Year] >= YEAR(NOW()) -1,

[MonthNumberOfYear] > MONTH( NOW()

          )

), 

AND( [Year] = YEAR(NOW()) , [MonthNumberOfYear] <= MONTH(NOW()))

),if(

AND( [Year] = YEAR(NOW()) , [MonthNumberOfYear] > MONTH(NOW()) )

,0,1)

,0)

This returns "1" for all dates in the 12 month window ending at the end of the current month, and 0 otherwise.

Then in the Commits table, I detected the case where there is no filter applied and gave a default behavior.  Importantly I used the ISCROSSFILTERED function which returns TRUE if a field is filtered through a relationship or through a direct filter on itself. 

Total commits YoY% :=
IF (
NOT ( ISCROSSFILTERED ( Commits[committer.date] ) ),
CALCULATE (
DIVIDE (
[Total commits] - [Total Commits Same Period Last year],
[Total Commits Same Period Last year]
),
FILTER ( DateTable, [isLast12Months] = 1 )
),
DIVIDE (
[Total commits] - [Total Commits Same Period Last year],
[Total Commits Same Period Last year]
)
)

Here's the final result. 

Note that the filtered dates all have the same result as before and the Grand Total has an understandable result.   Based on your requirements, you can change the Filter that is applied to get the result you desire.

HTH,

Lukasz