DAX Time Intelligence custom previous periods

Question

My cube has a fact table with a "Sales" column.

There is a related Date Table "SalesDate" (properly marked as a Date Table) I created a measure for "average sales" called [AvgSales]

There is also a measure for "past year average sales"

[AvgSales] := 
   AVERAGE([Sales])

[PY AvgSales] :=
IF (
    HASONEVALUE ( 'SalesDate'[Date] ),
    CALCULATE (
        [AvgSales],
        DATEADD ( 'SalesDate'[Date], -1, YEAR )
    ),
    BLANK ()
)

This works beautifully, and I can slice it in Excel like this: SalesDate[Year] on rows, SalesDate[Month] on columns.

The task at hand is to write a "past 5 year average sales" measure.

It is important that this measure will also work properly if you slice like described above (years on rows, months on columns)

I've spent a lot of time on http://www.daxpatterns.com/time-patterns/ but I'm really confused how to approach this properly.

Are you using a custom calendar? Otherwise, why not just use the built-in time intelligence functions? — Kyle Hale
I'm struggling to solve this with the built- in time intelligence functions. I can easily implement a comparison with any previous year. I'm struggling with comparing a single year with a 5-year period. What I want to do is compare the Average of e.g. 2016 with the ONE average over the 5-year period (2011, 2012, ... , 2016) - NOT the 5 single year averages. — Alexander
Ah, I see. So for any given year-month (say, Jan 2016), it would look back over Jan 2011, Jan 2012, Jan 2013, Jan 2014, and Jan 2015 and compute the average sales for those year-months? — Kyle Hale
As long as your date table contains all the dates you want to filter your Sales table on, you can use this: stackoverflow.com/questions/31796171/… — Kyle Hale

BrentA BrentA · Accepted Answer · 2016-05-20T22:11:15

This might be a bit simplistic but cant you just change the DATEADD function to -5 years?

[AvgSales] := AVERAGE([Sales])

[PY AvgSales] := IF ( HASONEVALUE ( 'SalesDate'[Date] ), CALCULATE ( [AvgSales], DATEADD ( 'SalesDate'[Date], -5, YEAR ) ), BLANK () )

DAX Time Intelligence custom previous periods

1 Answers