PowerBI - DAX : 12Month Rolling Average formula not working accurately

[Anonymous]

Hi Team,

I have created a DAX measure to calculate the 12 Month Rolling Average. The results looks very strange. The DAX measure works correctly only for certain months. 

The calculation starts from the Month 2020, Jan.  Till 2021, May the calculation works correctly and producing the expected results.

After 2021, May - The calculation does not produce accurate results. Its not returning 12 Month Average, the results are volatile. Please refer the attached excel sheet.

The measure I am using for calculating 12 Month Average is :

M22_12M Average to Case Resolution =

VAR LastDateinPeriod=MAX('Ref.Date'[Date])

Var FirstCaseinPeriod=NOT(ISEMPTY(FILTER(ALL('111_BLP_NewCases'),'111_BLP_NewCases'[Date]<LastDateinPeriod)))

Var LastCaseinPeriod=NOT(ISEMPTY(FILTER(ALL('111_BLP_NewCases'),'111_BLP_NewCases'[Date]>LastDateinPeriod)))

 F(NOT(ISBLANK(_12MAvegResolutioninPeriod)),_12MAvegResolutioninPeriod,IF(AND(FirstCaseinPeriod,LastCaseinPeriod),0))

---------------------------------------------------------------------------------------------------------------------------------------------- 

M21_3M_Avergae time to Case Resolution =

Var LastDateinPeriod=MAX('Ref.Date'[Date])

Var FirstCaseinPeriod=NOT(ISEMPTY(FILTER(ALL('111_BLP_NewCases'),'111_BLP_NewCases'[Date]<LastDateinPeriod)))

Var LastCaseinPeriod=NOT(ISEMPTY(FILTER(ALL('111_BLP_NewCases'),'111_BLP_NewCases'[Date]>LastDateinPeriod)))

Var _AvgTimeToResolution=([M20_3M_Sum of closed plus resolved case Age]/[M18_3M_count of closed and resolved cases])

Return

IF(NOT(ISBLANK(_AvgTimeToResolution)),_AvgTimeToResolution,IF(AND(FirstCaseinPeriod,LastCaseinPeriod),0))

------------------------------------------------------------------------------------------------------------------------------------------------

Year	Month	M21_3M_Avergae time to Case Resolution	M22_12M Average to Case Resolution	Month
2020	January	0	0	0
2020	February	0	0	0
2020	March	0	0	0
2020	April	0	0	0
2020	May	0	0	0
2020	June	7	1.166666667	6
2020	July	43.6	7.228571429	7
2020	August	142.78	24.1725	8
2020	September	159.75	39.23666667	9
2020	October	266.75	61.988	10
2020	November	805	129.5345455	11
2020	December	50.34	122.935	12
2021	January	34.77	125.8325	12
2021	February	44.25	129.52	12
2021	March	53.11	133.9458333	12
2021	April	60.81	139.0133333	12
2021	May	61.89	144.1708333	12
2021	June	59.86	150.7525	11.82673588
2021	July	54.68	168.7466667	10.63126185
2021	August	52.17	106.4066667	16.00820751
2021	September	57.51	111.1991667	14.39884891
2021	October	62.29	116.39	12
2021	November	61.7	52.54333333	12.43506947
2021	December	55.11	54.43916667	12.08964134
2022	January	55.82	58.14	11.68214654
2022	February	57.94	58.3875	11.86709484
2022	March	58.69	58.62083333	11.91504727
2022	April	54.34	57.47	12.0410649
2022	May	52.19	56.09166667	12.16401723
2022	June	51.74	55.4825	12.15121885
2022	July	53.94	56.6075	11.8966568
2022	August	51.02	57.16083333	11.76137507
2022	September	48.57	55.14333333	12.02955933
2022	October	41.95	52.4525	12.25890091
2022	November	39.16	51.37833333	12.07649139
2022	December	35.82	51.48083333	11.67774414
2023	January	32.95	47.935	12.06446229
2023	February	28.16	44.955	12.20175731
2023	March	21.32	42.5025	12.02658667
2023	April	17.79	39.49416667	12.01721773
2023	May	12.81	36.23166667	12.01242007
2023	June	1.2	32.6425	11.78494294

[Anonymous]

Hi, Thanks for your reply. The file is loaded with many tables already max - 80 tables. Also the file is having some sensitive information. I am sorry, I am unable to share the file.

[Wilson_]

Alright, best of luck with your problem. 🙂

[Wilson_]

Hello,

Can you please provide your pbix (or at least a sample version)?