Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 )) |
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.
Solved! Go to 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
Correct pattern of rolling period excluding 0
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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))
Hi @amitchandak thanks for your response.
[NPS_Modified] is a measure. The formula behind it is :
[NPS_Score] is also a measure.
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?
@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
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |