Forum Discussion

sergiogonzalez's avatar
sergiogonzalez
Regular Visitor
6 years ago
Solved

Calculate date between changes of status

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 i...
Show and Tell
  • Anonymous's avatar
    Anonymous
    6 years ago

    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. 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
sergiogonzalez
Regular Visitor
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.