Calculate # of days in each status

[amvans_90]

I need to calculate the number of days between each status change for each ID.  Some IDs have a status with multiple update dates, so I need the MIN UPDATEDDATE for each status to calculate the number of days they stay in each status. I'm trying to use the number of days in each status to create a Sankey chart.

Thanks for all suggestions!

[SD_308 · Accepted solution]

Hey amvans_90,

Thanks for sharing your problem! If I were approaching this, I would think of it in 2 ways. Either through Power Query or through a DAX Column dependant on data size and performance requirements.

Option 1 Power Query:

1. Duplicate your table so you have an additional version

2. In the new table, group by the following:

3. In your main table merge your grouped table by the ID and STATUS:

4. Expand your new column to bring in just the min status date: 

Option 2 Dax Column:

Create the following DAX Column:

Min Update Date =

CALCULATE(

    MIN (Table1[UPDATEDDATE]),

    ALLEXCEPT (Table1,Table1[ID],Table1[STATUS]))

Now that you have calculated the min date of each status just use a date diff function either in power query or DAX to get the number of days. 

All the best,

SD

[cengizhanarslan · Accepted solution]

Please try the logic below:

Days In Status =
VAR _CurrentID = MIN ( 'Table'[ID] )
VAR _CurrentStatus = MIN ( 'Table'[STATUS] )
VAR _StatusStart =
    CALCULATE (
        MIN ( 'Table'[UPDATEDDATE] ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[STATUS] )
    )
VAR _NextStatusStart =
    CALCULATE (
        MIN ( 'Table'[UPDATEDDATE] ),
        ALLEXCEPT ( 'Table', 'Table'[ID] ),
        'Table'[UPDATEDDATE] > _StatusStart,
        'Table'[STATUS] <> _CurrentStatus
    )
RETURN
    IF (
        NOT ISBLANK ( _NextStatusStart ),
        DATEDIFF ( _StatusStart, _NextStatusStart, DAY ),
        DATEDIFF ( _StatusStart, NOW (), DAY )
    )

[amvans_90]

Thanks! The starting status days didn't want to calculate correctly, so I made a new column to get min update date for each status, and replaced UPDATEDDATE in your dax with the Min Update Date column.  It worked!

[oussamahaimoud]

Hi @amvans_90,

Hope you are doing well!

Try this solution step by step :

Step 1: Create a Helper Table (Min Date per ID + Status)

Create a calculated table to get the MIN date per ID/Status combination:

StatusMinDates = 

SUMMARIZE(

    YourTable,

    YourTable[ID],

    YourTable[STATUS],

    "MinDate", MIN(YourTable[UPDATEDDATE])

)

Step 2: Add Days in Status Column

Add a calculated column to StatusMinDates that computes days between consecutive statuses:

DaysInStatus = 

VAR CurrentID = StatusMinDates[ID]

VAR CurrentStatus = StatusMinDates[STATUS]

VAR CurrentMinDate = StatusMinDates[MinDate]

-- Define status order

VAR StatusOrder = 

    SWITCH(CurrentStatus,

        "Draft", 1,

        "Pending Review", 2,

        "Pending Approval", 3,

        "Approved", 4,

        99

    )

-- Find the next status's min date for same ID

VAR NextMinDate = 

    MINX(

        FILTER(

            StatusMinDates,

            StatusMinDates[ID] = CurrentID &&

            SWITCH(StatusMinDates[STATUS],

                "Draft", 1,

                "Pending Review", 2,

                "Pending Approval", 3,

                "Approved", 4,

                99

            ) = StatusOrder + 1

        ),

        StatusMinDates[MinDate]

    )

RETURN

    IF(

        ISBLANK(NextMinDate),

        BLANK(), -- Last status has no "days" (or use TODAY() if still active)

        DATEDIFF(CurrentMinDate, NextMinDate, DAY)

    )

Step 3: For the Sankey Chart

Your Sankey needs From → To pairs. Create this calculated table:

SankeyFlows = 

VAR Base = 

    SUMMARIZE(

        YourTable,

        YourTable[ID],

        YourTable[STATUS],

        "MinDate", MIN(YourTable[UPDATEDDATE])

    )

RETURN

ADDCOLUMNS(

    Base,

    "StatusOrder", 

        SWITCH([STATUS],

            "Draft", 1,

            "Pending Review", 2,

            "Pending Approval", 3,

            "Approved", 4

        ),

    "NextStatus",

        VAR CurrID = [ID]

        VAR CurrOrder = SWITCH([STATUS],"Draft",1,"Pending Review",2,"Pending Approval",3,"Approved",4)

        RETURN

            MAXX(

                FILTER(Base,

                    [ID] = CurrID &&

                    SWITCH([STATUS],"Draft",1,"Pending Review",2,"Pending Approval",3,"Approved",4) 

                    = CurrOrder + 1

                ),

                [STATUS]

            ),

    "DaysInStatus",

        VAR CurrID = [ID]

        VAR CurrDate = [MinDate]

        VAR CurrOrder = SWITCH([STATUS],"Draft",1,"Pending Review",2,"Pending Approval",3,"Approved",4)

        VAR NextDate = 

            MINX(

                FILTER(Base,

                    [ID] = CurrID &&

                    SWITCH([STATUS],"Draft",1,"Pending Review",2,"Pending Approval",3,"Approved",4) 

                    = CurrOrder + 1

                ),

                [MinDate]

            )

        RETURN DATEDIFF(CurrDate, NextDate, DAY)

)

Summary:

StatusOrder: Defines the flow sequence

NextStatus: The "To" node for Sankey

DaysInStatus: Weight/thickness of Sankey link

Then:

Filter out BLANKs in NextStatus (last status rows) before connecting to your Sankey visual

For the last active status, replace BLANK() with DATEDIFF(CurrentMinDate, TODAY(), DAY) if you want to count ongoing days

Hope this helps! Feel free to ask more if needed. Don't forget to mark as a solution 🙂

[Jellywtpi]

How about having a new calculated table to get only your id, status, and the mindate for each id-status.

Then, for the days in status, this might be a new calculated column like this table. In this column, just take the date of the later stage - this stage, then you get the days in status.

But, i have a problem, for example, if approved is the final stage, then we have several options, for instance:

1. Take today as the reference day to calculate the days in status, or just choose another end date.

2. Value as 0 as it is the final.

...

Its your choice anyway.

About the dax :

Daysinstatus =
VAR CurrentID = StatusMinDates[ID]
VAR CurrentMinDate = StatusMinDates[MinDate]

VAR NextDate = CALCULATE( MIN(StatusMinDates[MinDate]), FILTER( ALL(StatusMinDates), StatusMinDates[ID] = CurrentID && StatusMinDates[MinDate] > CurrentMinDate ))

RETURN IF( ISBLANK(NextDate), DATEDIFF(CurrentMinDate, TODAY(), DAY), DATEDIFF(CurrentMinDate, NextDate, DAY))

[SD_308 · Accepted solution]

Hey amvans_90,

Thanks for sharing your problem! If I were approaching this, I would think of it in 2 ways. Either through Power Query or through a DAX Column dependant on data size and performance requirements.

Option 1 Power Query:

1. Duplicate your table so you have an additional version

2. In the new table, group by the following:

3. In your main table merge your grouped table by the ID and STATUS:

4. Expand your new column to bring in just the min status date: 

Option 2 Dax Column:

Create the following DAX Column:

Min Update Date =

CALCULATE(

    MIN (Table1[UPDATEDDATE]),

    ALLEXCEPT (Table1,Table1[ID],Table1[STATUS]))

Now that you have calculated the min date of each status just use a date diff function either in power query or DAX to get the number of days. 

All the best,

SD

[amvans_90]

Thanks! I used the DAX column you suggested to get the min update date for each status, then the DAX column suggested from @cengizhanarslan  to calculate the days.

[Monzii]

Based on your model you could group by ID and Status then use MIN of the 3rd field to derive at 1st date of achieving status for a particular ID.

[Ashish_Mathur]

Hi,

Please share data in a format that can be pasted in an MS Excel file.  Show the expected result as well.

[lbendlin]

As usual, GroupKind.Local and the fifth parameter to the rescue

Table.Group: Exploring the 5th element in Power BI and Power Query –

@ImkeF