z-score
1 TopicAdjusting rolling Z-Score for new items
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/2019Solved1.8KViews0likes2Comments
