Adjusting rolling Z-Score for new items

TMO_KY — Wed, 28 Sep 2022 12:55:59 GMT

Good Morning Community,

so I have a report that calculates rolling Z-Scores month to month, in order to do that, I have other rolling measures, thanks to this community, I found most of my answers/coding. For example, we trend codes and calculate the Z-Score over the previous 24 months of code usage. This is great for codes that have been used for a while, works perfectly. However, the one problem I'm running into now is when something new is added, it throws the Z-Score way above our "breach" line. Last month we introduced 4 new codes, so because of that, I have a DAX table that will automatically add missing values to whatever months are missing (this is a necessity, explained below), so for the 4 new codes, the last 23 months are all 0's. My measures are working the way they were intended but now my Z-Score is way off.

So long story short is there a way in DAX to have measures exclude if X number rows = 0 and then return 0 for the Z-score. Kind of like an IF statement until we get above a threshold to start including the actual calculation?

z = (x – μ) / σ

z = standard score

x = Current Month Value

μ = Population Mean Value (previous 24 months)

σ = standard deviation

Here's an example of the new code being used.

Code	Amt	Month	Avg 24 m	Std Dev 24m	Z-Score
BC07	8	08/01/2022	0.33	1.60	4.80
BC07	0	07/01/2022
BC07	0	06/01/2022
BC07	0	05/01/2022
BC07	0	04/01/2022
BC07	0	03/01/2022
BC07	0	02/01/2022
BC07	0	01/01/2022
BC07	0	12/01/2021
BC07	0	11/01/2021
BC07	0	10/01/2021
BC07	0	09/01/2021
BC07	0	08/01/2021
BC07	0	07/01/2021
BC07	0	06/01/2021
BC07	0	05/01/2021
BC07	0	04/01/2021
BC07	0	03/01/2021
BC07	0	02/01/2021
BC07	0	01/01/2021
BC07	0	12/01/2020
BC07	0	11/01/2020
BC07	0	10/01/2020
BC07	0	09/01/2020
BC07	0	08/01/2020
BC07	0	07/01/2020
BC07	0	06/01/2020
BC07	0	05/01/2020
BC07	0	04/01/2020
BC07	0	03/01/2020
BC07	0	02/01/2020
BC07	0	01/01/2020
BC07	0	12/01/2019
BC07	0	11/01/2019
BC07	0	10/01/2019
BC07	0	09/01/2019
BC07	0	08/01/2019

Here's an example of a working code, also shows why I had to insert 0 when a code wasn't used to get the correct Z-Score for the previous 24 months. This particular code wasn't used for 4 months in a row BUT I needed to show that to get an accurate Avg/Std Deviation and Z-Score.

Code	Amt	Month	Avg 24 m	Std Dev 24m	Z-Score	Prev Month
KP01	1	08/01/2022	2.08	6.36	-0.17	1
KP01	2	07/01/2022
KP01	2	06/01/2022
KP01	2	05/01/2022
KP01	5	04/01/2022
KP01	0	03/01/2022
KP01	0	02/01/2022
KP01	0	01/01/2022
KP01	0	12/01/2021
KP01	1	11/01/2021
KP01	0	10/01/2021
KP01	0	09/01/2021
KP01	0	08/01/2021
KP01	0	07/01/2021
KP01	1	06/01/2021
KP01	3	05/01/2021
KP01	1	04/01/2021
KP01	0	03/01/2021
KP01	0	02/01/2021
KP01	0	01/01/2021
KP01	0	12/01/2020
KP01	0	11/01/2020
KP01	0	10/01/2020
KP01	32	09/01/2020
KP01	26	08/01/2020
KP01	2	07/01/2020
KP01	2	06/01/2020
KP01	0	05/01/2020
KP01	2	04/01/2020
KP01	1	03/01/2020
KP01	0	02/01/2020
KP01	1	01/01/2020
KP01	3	12/01/2019
KP01	0	11/01/2019
KP01	0	10/01/2019
KP01	0	09/01/2019
KP01	0	08/01/2019

Re: Adjusting rolling Z-Score for new items

Anonymous — Thu, 29 Sep 2022 10:02:40 GMT

Hi TMO_KY ,

It would be great if you could provide more information to describe this problem.

I don't quite understand what your specific needs are and whether you can provide an output that you expect.

Best Regards,

Neeko Tang

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

Re: Adjusting rolling Z-Score for new items

TMO_KY — Thu, 29 Sep 2022 13:06:20 GMT

Thank you for the reply but I ended up figuring out a way to do it. I ended up creating an index column on my table using the most recent date as index 1 per category[Code] then created a variable to compare the mostrecent [AMT] to the sum of previous 24 months and if it equals, return 0 otherwise return "z":

testZ-Score = VAR z = ( [_testPrev Month] - [_TEST24m moving average] ) / [_testStDv of last 24 months] var a = IF(CALCULATE(SUM(table2[Amt]),FILTER(table2,table2[index]=1)) = [sumPrev24],0,z) RETURN IFERROR ( a, BLANK () )

Here was my output, expected results vs what I was getting:

Code	index	Amt	sumPrev24	Original (Not Correct)	testZ-Score (expected results)
AI01	1	2	19	1.6	1.6
BC07	1	8	8	4.8	0

topic Adjusting rolling Z-Score for new items in DAX Commands and Tips

Adjusting rolling Z-Score for new items

Re: Adjusting rolling Z-Score for new items

Re: Adjusting rolling Z-Score for new items