Calculate date between changes of status

[sergiogonzalez]

Hi, i need to calculate the time between each change of status, i have an index (ID_HISTORIAL)

For example:
The time between 843453 and 844104 is 2 days.
The time between 844104 and 845402 is 4 days.

The time between 845402 and 846679 is 2 days.

In other case, ID_TAREA is different.

The time between 845018 and 846680 is 3 days.

All of those, grouped by ID_TAREA

Thanks

[v-rzhou-msft]

Hi @sergiogonzalez 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft]

Hi @sergiogonzalez 

I build a table like yours to have a test.

Table:

Build a Rank for each ID TARER by calculated column.

var _rank =

RANKX (

    FILTER ( 'Table', 'Table'[ID TARER] = EARLIER ( 'Table'[ID TARER] ) ),

    'Table'[FECHA ENTREGADO].[Day],

    ,

    ASC,

    DENSE

)

Result:

Then I build a measure to achieve your goal.

ESTADO =

VAR _MIN =

    MINX (

        FILTER (

            ALL ( 'Table' ),

            'Table'[ID TARER] = MAX ( 'Table'[ID TARER] )

                && 'Table'[var _rank] = MAX ( 'Table'[var _rank] )

                && CALCULATE (

                    COUNTROWS ( 'Table' ) >= 2,

                    FILTER (

                        ALL ( 'Table' ),

                        'Table'[ID TARER] = MAX ( 'Table'[ID TARER] )

                            && 'Table'[var _rank] = MAX ( 'Table'[var _rank] )

                    )

                )

        ),

        'Table'[FECHA ENTREGADO]

    )

VAR _DATE1 =

    MAXX (

        FILTER (

            ALL ( 'Table' ),

            'Table'[ID TARER] = SUM ( 'Table'[ID TARER] )

                && 'Table'[var _rank] < SUM ( 'Table'[var _rank] )

        ),

        'Table'[FECHA ENTREGADO]

    )

VAR _DATE2 =

    MAX ( 'Table'[FECHA ENTREGADO] )

RETURN

    IF (

        SUM ( 'Table'[var _rank] ) = 1,

        1,

        IF (

            MAX ( 'Table'[FECHA ENTREGADO] ) = _MIN,

            1,

            DATEDIFF ( _DATE1, _DATE2, DAY )

        )

    )

Result:

You can download the pbix file from this link: Calculate date between changes of status

Best Regards,

Rico Zhou

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

[sergiogonzalez]

Thanks, but i don't understand why you change the "estado" column, i need a new column with the times between each changes of column "estado", and in case of a repeated state, just count the first date.

Thanks

[v-rzhou-msft · Accepted solution]

Hi @sergiogonzalez 

I build a new table to achieve your goal.

Firstly I build an Index column like before.

var _rank =

RANKX (

    FILTER ( 'Table', 'Table'[ID TARER] = EARLIER ( 'Table'[ID TARER] ) ),

    'Table'[FECHA ENTREGADO].[Day],

    ,

    ASC,

    DENSE

)

Then I build a new calculated column to calculate the days between each changes of column "estado".

Time = 
VAR _Lastestado =
    CALCULATE (
        SUM ( 'Table'[ESTADO] ),
        FILTER ( 'Table', 'Table'[var _rank] = EARLIER ( 'Table'[var _rank] ) - 1 )
    )
VAR _DATE1 =
    MAXX (
        FILTER (
            'Table',
            'Table'[ID TARER] = EARLIER ( 'Table'[ID TARER] )
                && 'Table'[var _rank] < EARLIER ( 'Table'[var _rank] )
        ),
        'Table'[FECHA ENTREGADO]
    )
VAR _DATE2 = 'Table'[FECHA ENTREGADO]
RETURN
    IF (
        'Table'[var _rank] = 1,
        1,
        IF ( 'Table'[ESTADO] - _Lastestado <> 0, DATEDIFF ( _DATE1, _DATE2, DAY ), 1 )
    )

Result:

You can download the pbix file from this link: Calculate date between changes of status

Best Regards,

Rico Zhou

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

[amitchandak]

@sergiogonzalez ,

diff =
datediff( maxx(filter(table,table[ID_TAREA] = earlier([ID_TAREA]) && [ID_HISTORIAL] <earlier([ID_HISTORIAL])),[Fecha]),[Fecha], Day)

[sergiogonzalez]

Show me an error, parameter is not the correct type