Search code examples
excelpivot-tableformuladaxdatamodel

DAX for rolling seven day average to pivot chart by year

I'm trying to create a single pivot chart that will show separate years of data on the same date axis for a rolling 7-day average.

So, the x-axis will be text, 01-Jan to 31-Dec, and each year will be a separate series:

enter image description here

It has to be a text x-axis, as 01-Jan will be a category containing data for 01-Jan-2018, 01-Jan-2019, 01-Jan-2020...

In theory, the pivot table setup would have the column (series) as the Year, and the x-Axis (labels) as the date label (column Date).

The values are then from the DAX expression that creates the rolling 7-day average.

enter image description here

The source data (tblSource) has a single column of dates (Date2) that rolls over across years and has the column Year to break it down in the pivot.

The daily value is the one that is averaged (itself and the previous six days).

enter image description here

The 7-day average I normally use in DAX doesn't work here.

I need to have the Date column actually in the pivot rather than Date2, as the axis needs to be text to allow for multiple date years on the same x-axis point, but I can't get a DAX formula to work.

The other consideration is that the formula can't just consider a single year, as the rolling seven day average for 01-Jan-2018 includes the previous six days of 2017, for example.

This is the formula I usually use, but I can't manage to tweak it!

AVERAGEX (
    DATESINPERIOD ( tblSource[Date2], LASTDATE ( tblSource[Date2] ), -7, DAY ),
    [Sum of Daily Value]
)

enter image description here

But this is the output I get, and nothing has been averaged. I think it's because Date2 is being pivoted off Date, but I'm not sure how I get around that?

Can anybody offer me any help?

It's quite a frustrating problem as it would be trivial for me to do it using code, or doing it manually, but I really am trying to get better at DAX!

Thanks in advance!

Phil.

Update: Thanks to Joao for this.

=VAR d = MAX(tblSource[Date2])
RETURN CALCULATE(AVERAGE(tblSource[Daily Value]), 
    ALL(tblSource[Date]),
    DATESINPERIOD(tblSource[Date2], d, -7, day),tblSource[Year]>0)

I had to use MAX rather than SELECTVALUE as Excel seems to lack that functionality.

I also had to unfilter the Year, so that the rolling average could be calculated from the previous year's date where neccessary.

Thanks.

Solution

  • When you run that measure, your table is being filtered by the date text at each point, so when you pass/create the DATESINPERIOD filter, it creates a table with the last 7 dates, but only one of them is actually available (the one relevant to your current data point).

    You need to clear the filters on the table so that you have all the dates available, in order for you average to work. You can achieve this by changing the measure slightly:

    VAR d = SELECTEDVALUE(tblSource[Date2])
RETURN CALCULATE(AVERAGE(tblSource[Daily Value]), 
    ALL(tblSource[Date]),
    DATESINPERIOD(tblSource[Date2], d, -7, day))