cancel
Showing results 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

Helper III

## DAX in building Rolling 4 Week Average

Hi,

I need help in building 4 week rolling average for "Net Change in Price and Cost" with below sample data

Change in Price, Change in Cost and Net Change in Price and Cost are all 3 measures.

Net Change in Price and Cost = Change in Price - Change in Cost

I do have separate date table and the above data shows weekly data and my report has Invoice Date as Filter.

I need to calculate rolling 4 week average, here the tweak is that for first week start date 12/07 rolling average should be same while for next 12/14 it should (-4.80% + 0.18%)/2 = -2.31%

for 12/21 it should be (-4.80% + 0.18% - 0.04%)/3 = -1.55%

for 12/28 it should be (-4.80% + 0.18% - 0.04% -0.65%)/4 = -1.33%

for 01/04 it should be (0.18% - 0.04% -0.65% +1.03%)/4 = 0.13%

as shown below

I have created below DAX but its not working as expected

Net Change in Price and Cost 4 Wk Rolling Avg =
var start_day = MIN('Date'[Week Start Date])-21
var end_day = MAX('Date'[Week Start Date])

return
CALCULATE([Net Change in Price and Cost],
DATESBETWEEN('Date'[Week Start Date],start_day,end_day),
REMOVEFILTERS('Date'[Week Start Date]))/CALCULATE(DISTINCTCOUNT('Date'[Week Start Date]),DATESBETWEEN('Date'[Week Start Date],start_day,end_day),REMOVEFILTERS('Date'[Week Start Date]))

Can someone help me on this??

1 ACCEPTED SOLUTION
Community Support

Hi @apatwal ,

Here's my solution.

1.Create a index measure

``````Index =
CALCULATE (
COUNT ( 'Date'[Week Start Date] ),
FILTER (
ALL ( 'Date' ),
[Week Start Date] <= MAX ( 'Table'[Week Start Date] )
)
)
``````

2.Create another measure to get the rolling 4 week average.

``````Rolling 4 week average =
CALCULATE (
AVERAGEX ( 'Table', [Net Change in Prce and Cost] ),
FILTER (
ALL ( 'Table' ),
[Index] <= MAXX ( 'Table', [Index] )
&& [Index]
>= MAXX ( 'Table', [Index] ) - 3
)
)
``````

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5
Community Support

Hi @apatwal ,

Here's my solution.

1.Create a index measure

``````Index =
CALCULATE (
COUNT ( 'Date'[Week Start Date] ),
FILTER (
ALL ( 'Date' ),
[Week Start Date] <= MAX ( 'Table'[Week Start Date] )
)
)
``````

2.Create another measure to get the rolling 4 week average.

``````Rolling 4 week average =
CALCULATE (
AVERAGEX ( 'Table', [Net Change in Prce and Cost] ),
FILTER (
ALL ( 'Table' ),
[Index] <= MAXX ( 'Table', [Index] )
&& [Index]
>= MAXX ( 'Table', [Index] ) - 3
)
)
``````

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

New Member

Somehow it didn't work for me. Index part didn't work. I also do not have week start date in sales table. Is there anyway i can connect with you to fix the issue i have. Thank you so much. Appreciate !

Anonymous
Not applicable

Thanks so much. This really worked for me. Hope to learn a lot from these community

Super User

@apatwal , Create a week rank on week start date or on week year

new columns

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

Avg of rolling 4 weeks , assuming [Net Change in Price and Cost] is measure

Last 4 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), [Net Change in Price and Cost]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Helper III

But looks there is some issue in calculations

Used same DAX provided by you..

Rolling 4 Week Average =
CALCULATE(
AVERAGEX(
VALUES('Date'[Week Rank]),
MainTable[Net Change in Price and Cost]
),
FILTER(
ALL('Date'),
'Date'[Week Rank] >= MAX('Date'[Week Rank])-4 &&
'Date'[Week Rank] <= MAX('Date[Week Rank])
)
)

Announcements

#### 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 Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors