Help with DAX: Event status table

[hariganesh]

Hi

I have a situation where i have a table with entities with different stages, completion status, and status dates. I'm trying to calculate the average days spent between status changes (e.g. average time to move from status 1 to status 2). I was able to figure out a method to use calculate tables to calculate counts of entities completing multiple stages. I have attached a a picture below for details

Appreciate any help with this

Regards

Hari

[v-jiascu-msft · Accepted solution]

Hi @hariganesh,

Please try a measure like below.

Measure =
VAR newCompletedDate =
    IF (
        ISBLANK ( MAX ( 'Table1'[Completed Date] ) ),
        TODAY (),
        MIN ( 'Table1'[Completed Date] )
    )
VAR preDate =
    CALCULATE (
        MAX ( 'Table1'[Completed Date] ),
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[Completed Date] < newCompletedDate
                && 'Table1'[Entity] = MAX ( [Entity] )
        )
    )
RETURN
    DATEDIFF ( preDate, newCompletedDate, DAY )

Measure 2 =
AVERAGEX (
    SUMMARIZE ( 'Table1', Table1[Entity], Table1[Event], "Value", [Measure] ),
    [Value]
)

Best Regards,

Dale

[hariganesh]

Thank you, i ended up using a different method as the stages were dynamic and could grow,

[hariganesh]

For clarification, I'm looking to see if there's a way to calculate the average days between a set of stages (e.g. stage1-2, Stage 2-3) for completed entries as a measure.