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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.