Forum Discussion

Samoe's avatar
Samoe
Advocate I
2 years ago
Solved

Durations between different status

Hi all,

I think I am stuck somewhere in my head, I think this should actually be easy to solve...

I have an excel sheet (example with random numbers here) which is used to track the leadtime. I have the date of each status change, the lot number associated to it and I created a calculated column to convert the statut text into a number bc I thought it might help.

How can I now create a measure that shows me the time passed between status 1-2, 2-3, 3-4 and 1-4 total for each no. lot? I cannot seem to get it. Also, ideally it is in weekdays (Monday-Friday)....

 

Many thanks in advance for all help!

 

Edit: would if help if the data is ordered differently, with one line per no. lot and then each column header is a status and the dates of one no.lot are all in the same row?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Samoe 

    You can create folloing measure

    Measure =
VAR nextstepdate =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [No.lot]
                IN VALUES ( 'Table'[No.lot] )
                    && [Statut No]
                        = MAX ( 'Table'[Statut No] ) + 1
        ),
        [Date]
    )
RETURN
    IF (
        NOT ( ISBLANK ( nextstepdate ) ),
        DATEDIFF ( MAX ( 'Table'[Date] ), nextstepdate, DAY ),
        DATEDIFF (
            MINX (
                FILTER ( ALLSELECTED ( 'Table' ), [No.lot] IN VALUES ( 'Table'[No.lot] ) ),
                [Date]
            ),
            MAX ( 'Table'[Date] ),
            DAY
        )
    )

    Output

    Best Regards!

    Yolo Zhu

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

     

4 Replies

  • mh2587's avatar
    mh2587
    Super User
    2 years ago
    TimeBetweenStatus =//Try this one
VAR CurrentStatusNumber = MAX('LeadTime'[StatusNumber])
VAR NextStatusNumber =
CALCULATE(
MIN('LeadTime'[StatusNumber]),
'LeadTime'[StatusNumber] > CurrentStatusNumber
)
RETURN
IF(
ISBLANK(NextStatusNumber),
BLANK(),
CALCULATE (
MAX('LeadTime'[StatusDate]) - MAXX(
FILTER(
'LeadTime',
'LeadTime'[StatusNumber] = CurrentStatusNumber
),
'LeadTime'[StatusDate]
),
'LeadTime'[StatusNumber] = NextStatusNumber
)
)
    • Samoe's avatar
      Samoe
      Advocate I
      2 years ago

      Hi, thanks for the proposition! So far, it does not seem to work though... it returns nothing. How do I connect this calculation now to the NO.lot (the unique batch number)? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Samoe 

        You can create folloing measure

        Measure =
VAR nextstepdate =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [No.lot]
                IN VALUES ( 'Table'[No.lot] )
                    && [Statut No]
                        = MAX ( 'Table'[Statut No] ) + 1
        ),
        [Date]
    )
RETURN
    IF (
        NOT ( ISBLANK ( nextstepdate ) ),
        DATEDIFF ( MAX ( 'Table'[Date] ), nextstepdate, DAY ),
        DATEDIFF (
            MINX (
                FILTER ( ALLSELECTED ( 'Table' ), [No.lot] IN VALUES ( 'Table'[No.lot] ) ),
                [Date]
            ),
            MAX ( 'Table'[Date] ),
            DAY
        )
    )

        Output

        Best Regards!

        Yolo Zhu

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