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 hav...
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.

     

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.

     

    • DAIKAYA's avatar
      DAIKAYA
      Frequent Visitor
      2 years ago

      Hello ,

       

      May I know how did u attach the Pbix here?