Z-Score DAX Measure

[rgadbois]

Hi,

I am attempting to create a scatter plot visual in which I can look at the Z-score of two separate metrics (one on X-axis, one on Y-axis) on a near-daily basis. What my variables are for defining the parameters of the Z-score are as follows:

Mean: 28-day rolling average

Standard Deviation: 28-Day total STDEV

X: (Almost) daily value

Below is the DAX measure I created:

_PL Test =

VAR PL28Rolling = CALCULATE(AVERAGE(Catapult[PL]), ALL(Dates), AND(Dates[week index] <= -1, 'Dates'[week index] >= -4))

VAR PL28RollSTD = CALCULATE(STDEV.P(Catapult[PL]), ALL(Dates), AND(Dates[week index] <= -1, 'Dates'[week index] >= -4))

VAR DailyPL = CALCULATE(AVERAGE(Catapult[PL]), ALL(Dates), Dates[day index] = 0)

RETURN

(DailyPL - PL28Rolling) / PL28RollSTD

My date table linked to the "Catapult" table appears as follows:

The issue I am running into is that, such as for today, it appears that PowerBI is calculating values for days which have no data, and making them a "0". My values range as below, but these values are much larger than should be expected:

Any help on this would be greatly appreciated! I am still pretty new to DAX and creating complex measures such as this.

Thank you!

[Anonymous]

Hi @rgadbois ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu