topic Re: Rolling average of measure outcome over time taking 4 days in advance and 4 days forward in DAX Commands and Tips

Rolling average of measure outcome over time taking 4 days in advance and 4 days forward

swooters — Fri, 24 Jul 2020 10:18:10 GMT

In my data model I have a total capacity measure which I show on date level. This is the field "FlexLeveling_1 Total FixedFlex". Basically this sums the columns "Benodigde fixed cap (bruto)" and "Benodigde flex cap (netto)" as you can see in the picture below.

Now I want to show a Rolling Average of "FlexLeveling_1 Total FixedFlex" for every date. This rolling average must look back 4 days back and 4 days forward. So basically it covers 9 days of which the selected date is in the middle.

Example:

Period	FlexLeveling_1 Total FixedFlex	Rolling average	Average over periods
1	11
2	47
3	45
4	44
5	23	35,78	1 to 9
6	34	35,89	2 to 10
7	50	33,78	3 to 11
8	43	30,22	4 to 12
9	25	28,67	5 to 13
10	12	31,00	6 to 14
11	28
12	13
13	30
14	44

I am now applying the syntax below but it gives me unexpected results. Who can help me?

FlexLeveling_2 Rolling average =

AVERAGEX(

FILTER(

ALL('Result cap'[Date]),

'Result cap'[Date]>=DATEADD(LASTDATE('Result cap'[Date]),-4,DAY)&&

'Result cap'[Date]<=DATEADD(LASTDATE('Result cap'[Date]),4,DAY)),

[FlexLeveling_1 Total FixedFlex])

Re: Rolling average of measure outcome over time taking 4 days in advance and 4 days forward

amitchandak — Fri, 24 Jul 2020 12:14:22 GMT

swooters ,

Try like examples

Rolling4 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-4,Day))

Rolling 4 plus 4 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date])-4,8,Day))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

Appreciate your Kudos.

Re: Rolling average of measure outcome over time taking 4 days in advance and 4 days forward

swooters — Fri, 24 Jul 2020 13:03:50 GMT

amitchandak

I tried to adopt your tips. This is what I did:

FlexLeveling_2 Rolling average =

CALCULATE(

AVERAGEX(

VALUES('Dates future'[Date]),[FlexLeveling_1 Total FixedFlex]),

DATESINPERIOD('Dates future'[Date],MAX('Result cap'[Date])-4,8,DAY))

As you can see I now make use of a date calendar, which is generated with the CALENDAR() function.

However, I am not getting the result I want. Here is the output (please look at the last column)

The output from 6 Jan onwards should be around 4 or 5.

I also added an explanatory example:

What am I doing wrong?