topic Weekly rolling average to appear in weeks with no values in DAX Commands and Tips

Weekly rolling average to appear in weeks with no values

Anonymous — Sat, 13 May 2023 23:34:53 GMT

Hello, no DAX expert here but curious to know the solution. I have a martix table in PBI with 5 layers of detail: week num, region, cat, sub cat, sub cat 2. I need to calculate rolling averages for each level of detail, which is fine until I get to the most granular level, sub cat 2, which doesn't have actual values to average every week. I still, however, want to see the rolling average. Is there a way to do that?

This is how we calc rolling averages.

Thank you so much in advance!!!!!

Re: Weekly rolling average to appear in weeks with no values

v-yueyunzh-msft — Mon, 15 May 2023 02:19:00 GMT

Hi , Anonymous

According to your image and description , you want to calcuate the previous 4weeks rolling average.And you also want to calcualte when it has no value.

Here are the steps you can refer to :
(1)This is my test data:

(2)We need to click "New Table" to create a table like this:

Table 2 = DISTINCT( SELECTCOLUMNS('Table',"Region",'Table'[Region],"Major Cat",'Table'[Major Cat] , "Sub Cat",'Table'[Sub Cat] , "Sub Cat2" , 'Table'[Sub Cat2]))

(3)Then we need to click "New Measure" to create a measure like this:

Measure 2 = var _cur_cat2 = MAX('Table 2'[Sub Cat2]) var _cur_sub = MAX('Table 2'[Sub Cat]) var _major_cat = MAX('Table 2'[Major Cat]) var _region = MAX('Table 2'[Region]) var _week = MAX('Table'[Week_Sort]) var _t = FILTER( ALLSELECTED('Table') , 'Table'[Region]=_region && 'Table'[Major Cat]=_major_cat && 'Table'[Sub Cat] = _cur_sub && 'Table'[Sub Cat2]=_cur_cat2 && 'Table'[Week_Sort]<= _week &&'Table'[Week_Sort]>_week-4) return IF(_week<4 , BLANK() ,SUMX(_t,[Weekly Sales])/4 )

Then we can put these fields on the visual and we can get this:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

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

Re: Weekly rolling average to appear in weeks with no values

Anonymous — Mon, 15 May 2023 19:12:35 GMT

Thank you very much, Aniya - i believe this will do it!