Weekly rolling average to appear in weeks with no values

[Anonymous]

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

[v-yueyunzh-msft · Accepted solution]

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

[Anonymous · Accepted solution]

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

[v-yueyunzh-msft · Accepted solution]

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