Standard deviation - Days with no transaction

[Anonymous]

Hello, Stdev.P doesn't take into account when there is no record on a date for an item.

In this case, Item C has transactions on Jan 1st and 3rd, so it returns the Stdev for those 2 days (2,5).

The calculus is wrong since there should be a 0 value for Jan 2nd taken into account in the calculus.

The result would then be 4,082.

Is there any workaround in DAX to do it? I'm able to do it in PQ with a full outer join but it is way to heavy when you have a big data set.

My data Data set.

       

Thank you

[Anonymous · Accepted solution]

Hi @Anonymous ,

I have created a simple sample, please refer to it to see if it helps you.

Create 2 measures.

Measure2 =
VAR _MAXDATE =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[item] = SELECTEDVALUE ( 'Table'[item] ) ),
        'Table'[date]
    )
VAR _MINDATE =
    MINX (
        FILTER ( ALL ( 'Table' ), 'Table'[item] = SELECTEDVALUE ( 'Table'[item] ) ),
        'Table'[date]
    )
VAR _DATEDIFF =
    DATEDIFF ( _MINDATE, _MAXDATE + 1, DAY )
VAR _ave =
    AVERAGEX (
        FILTER ( ALL ( 'Table' ), 'Table'[item] = SELECTEDVALUE ( 'Table'[item] ) ),
        'Table'[qTY]
    )
VAR _re =
    (
        ( MAX ( 'Table'[qTY] ) - _ave ) ^ 2 / _DATEDIFF
    )
RETURN
    _re

Measure3 =
VAR _MAXDATE =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[item] = SELECTEDVALUE ( 'Table'[item] ) ),
        'Table'[date]
    )
VAR _MINDATE =
    MINX (
        FILTER ( ALL ( 'Table' ), 'Table'[item] = SELECTEDVALUE ( 'Table'[item] ) ),
        'Table'[date]
    )
VAR _COUNT =
    COUNTAX (
        FILTER ( ALL ( 'Table' ), 'Table'[item] = SELECTEDVALUE ( 'Table'[item] ) ),
        'Table'[date]
    )
VAR _DATEDIFF =
    DATEDIFF ( _MINDATE, _MAXDATE + 1, DAY )
VAR _VALUE =
    STDEVX.P (
        FILTER ( ALL ( 'Table' ), 'Table'[item] = SELECTEDVALUE ( 'Table'[item] ) ),
        'Table'[qTY]
    )
VAR _ave =
    AVERAGEX (
        FILTER ( ALL ( 'Table' ), 'Table'[item] = SELECTEDVALUE ( 'Table'[item] ) ),
        'Table'[qTY]
    )
VAR _re =
    SUMX (
        FILTER ( ALL ( 'Table' ), 'Table'[item] = SELECTEDVALUE ( 'Table'[item] ) ),
        [Measure2]
    )
VAR _sqrt =
    SQRT ( _re )
RETURN
    IF ( _COUNT = _DATEDIFF, _VALUE, IF ( _DATEDIFF > _COUNT, _sqrt, BLANK () ) )

Best Regards

Community Support Team _ Polly

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

[Anonymous]

Hello, the result is not 4,082. Thank you

[johnt75]

Try

Qty Std Dev =
STDEVX.P (
    VALUES ( 'Date'[Date] ),
    COALESCE ( CALCULATE ( SUM ( 'Table'[Qty] ) ), 0 )
)

[Anonymous]

Hello @johnt75 , I get zeros... Thank you