Rolling average excluding 0

[adlsh11]

Hi all,

I have a working rolling average formula below:

NPS rolling avg (30days)= CALCULATE(AVERAGEX(NPS_Query, [NPS_Modified]),DATESINPERIOD(DimDate[Date] ,LASTDATE(DimDate[Date] ) , -30, DAY ))

The issue with the formula above is it includes days where the value NPS_Modified = 0.

The formula below aims to do the same as above, but excludes NPS Modified = 0.

NPS rolling avg (30days) = CALCULATE(AVERAGEX(NPS_Query, [NPS_Modified]), DATESINPERIOD(DimDate[Date] ,LASTDATE(DimDate[Date] ) , -30, DAY ), FILTER(NPS_Query,[NPS_Modified]<>0))

The second formula does not return any error, but it returns the wrong results.

Dataset .csv format below:

NPS_Modified,Date,NPS rolling avg (30days)
60,2021-12-10 00:00:00,"60,00"
25,2021-12-11 00:00:00,"33,33"
50,2021-12-12 00:00:00,"100,00"
-44,2021-12-13 00:00:00,"-50,00"
-8,2021-12-14 00:00:00,"-11,11"
-50,2021-12-15 00:00:00,"-50,00"
100,2021-12-16 00:00:00,"100,00"
50,2021-12-17 00:00:00,"100,00"
50,2021-12-18 00:00:00,"50,00"
0,2021-12-19 00:00:00,"0,00"
21,2021-12-20 00:00:00,"27,27"
20,2021-12-21 00:00:00,"20,00"
100,2021-12-22 00:00:00,"100,00"
29,2021-12-23 00:00:00,"50,00"
0,2021-12-24 00:00:00,
33,2021-12-25 00:00:00,"33,33"
33,2021-12-26 00:00:00,"33,33"
25,2021-12-27 00:00:00,"27,27"
0,2021-12-28 00:00:00,"0,00"
40,2021-12-29 00:00:00,"100,00"
75,2021-12-30 00:00:00,"100,00"
-25,2021-12-31 00:00:00,"-33,33"
60,2022-01-01 00:00:00,"60,00"
17,2022-01-02 00:00:00,"25,00"
54,2022-01-03 00:00:00,"63,64"
-33,2022-01-04 00:00:00,"-38,46"
-6,2022-01-05 00:00:00,"-6,67"
8,2022-01-06 00:00:00,"11,11"
0,2022-01-07 00:00:00,"0,00"
100,2022-01-08 00:00:00,"100,00"
60,2022-01-09 00:00:00,"60,00"
-18,2022-01-10 00:00:00,"-20,00"
29,2022-01-11 00:00:00,"33,33"
31,2022-01-12 00:00:00,"40,00"
40,2022-01-13 00:00:00,"50,00"
71,2022-01-14 00:00:00,"71,43"
0,2022-01-15 00:00:00,"0,00"
50,2022-01-16 00:00:00,"100,00"
25,2022-01-17 00:00:00,"27,27"
63,2022-01-18 00:00:00,"100,00"
78,2022-01-19 00:00:00,"77,78"
-33,2022-01-20 00:00:00,"-33,33"
67,2022-01-21 00:00:00,"66,67"
100,2022-01-22 00:00:00,"100,00"
0,2022-01-23 00:00:00,"0,00"
18,2022-01-24 00:00:00,"25,00"
78,2022-01-25 00:00:00,"100,00"
18,2022-01-26 00:00:00,"20,00"
40,2022-01-27 00:00:00,"50,00"
50,2022-01-28 00:00:00,"50,00"
-14,2022-01-29 00:00:00,"-20,00"
50,2022-01-30 00:00:00,"100,00"
0,2022-01-31 00:00:00,"0,00"
-25,2022-02-01 00:00:00,"-27,27"
29,2022-02-02 00:00:00,"50,00"
-25,2022-02-03 00:00:00,"-33,33"
100,2022-02-04 00:00:00,"100,00"
0,2022-02-05 00:00:00,
0,2022-02-06 00:00:00,
0,2022-02-07 00:00:00,

The NPS rolling avg (30 days) returns the numbers that I deem wrong. 

[CNENFRNL]

Correct pattern of rolling period excluding 0

[amitchandak]

@adlsh11 ,

NPS rolling avg (30days) = CALCULATE(AVERAGEX(FILTER(NPS_Query,[NPS_Modified]<>0), [NPS_Modified]), DATESINPERIOD(DimDate[Date] ,LASTDATE(DimDate[Date] ) , -30, DAY ) )

if NPS_Modified is a measure , try like
NPS rolling avg (30days) = CALCULATE(AVERAGEX(values(DimDate[Date]), [NPS_Modified]), DATESINPERIOD(DimDate[Date] ,LASTDATE(DimDate[Date] ) , -30, DAY ), FILTER(NPS_Query,[NPS_Modified]<>0))

[adlsh11]

Hi @amitchandak thanks for your response. 

[NPS_Modified] is a measure. The formula behind it is : 

IF(ISBLANK(NPS_Measures[NPS_Score]),0,NPS_Measures[NPS_Score])

[NPS_Score] is also a measure.

NPS_Score = IF(([Passive%] = 100), 0, ROUND(

[Promoter%] - [Detractor%], 0 ))

Trying the rolling average formula for 30 and 90 days, I seem to get almost identical results. Besides, the rolling averages in the last 3 days are blank. Could you explain why, and whether this makes sense? 

[amitchandak · Accepted solution]

@adlsh11 , For Avg you should try

NPS rolling avg (30days)= CALCULATE(AVERAGEX(values(DimDate[Date]), [NPS_Score]),DATESINPERIOD(DimDate[Date] ,LASTDATE(DimDate[Date] ) , -30, DAY ))

NPS rolling avg (90days)= CALCULATE(AVERAGEX(values(DimDate[Date]), [NPS_Score]),DATESINPERIOD(DimDate[Date] ,LASTDATE(DimDate[Date] ) , -90, DAY ))

If you have data for 90 days, the value should be different , but avg can near by