Durations between different status

[Samoe]

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?

[mh2587]

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]

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 · Accepted solution]

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]

Hello ,

May I know how did u attach the Pbix here?