Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
swooters
Regular Visitor

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

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.

 

swooters_0-1595585225452.png

 

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:

PeriodFlexLeveling_1 Total FixedFlexRolling averageAverage over periods
111  
247  
345  
444  
52335,781 to 9
63435,892 to 10
75033,783 to 11
84330,224 to 12
92528,675 to 13
101231,006 to 14
1128  
1213  
1330  
1444  


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])
2 REPLIES 2
amitchandak
Super User
Super User

@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-Y...


Appreciate your Kudos.

 

@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)
 

Output2.PNG

 

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

 

I also added an explanatory example:

 

 

 

What am I doing wrong?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors