Forum Discussion

hariganesh's avatar
hariganesh
Frequent Visitor
8 years ago
Solved

Help with DAX: Event status table

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's avatar
    v-jiascu-msft
    8 years ago

    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]
)

    Help_with_DAX_Event_status_table

    Help_with_DAX_Event_status_table2

     

    Best Regards,

    Dale

3 Replies

  • hariganesh's avatar
    hariganesh
    Frequent Visitor
    8 years ago

    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.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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]
)

    Help_with_DAX_Event_status_table

    Help_with_DAX_Event_status_table2

     

    Best Regards,

    Dale

    • hariganesh's avatar
      hariganesh
      Frequent Visitor
      8 years ago

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