DAX in building Rolling 4 Week Average

[apatwal]

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??

[v-stephen-msft · Accepted solution]

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.

[stellasun820]

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]

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

[amitchandak]

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

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[apatwal]

Hi @amitchandak 

thanks for your reply!

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

)

)