CODEWITHSUNDEEP
excelpowerbidaxpowerpivot
zzz-zzz
zzz-zzz

Reputation: 19

DAX : Find process time & last operation from date/time difference

Need your help to find "Process Time" in hours and where it in last operation based on Max Operation number 

DATEDIFF(
    CALCULATE(
        SUM(tableX[date/time]),
        ALLEXCEPT(tableX,tableX[Operation],tableX[ID]),
        tableX[date/time] <= EARLIER(tableX[date/time])
   ), 
   tableX[date/time],HOUR
)

capture

Upvotes: 0

Views: 106

Answers (1)

Marco Vos
Marco Vos

Reputation: 2968

I think you are looking for the following calculated column:

Process Time (Hours) = DATEDIFF(
    CALCULATE(
        MAX('tableX'[Date/Time]),
        ALLEXCEPT(tableX,'tableX'[ID]),
        'tableX'[date/time] < EARLIER('tableX'[date/time])
   ), 
   'tableX'[Date/Time],HOUR
)

This expression calculates the elapsed time sinds the previous step in the operation. If you want to calculate the elapsed time sinds the start of the operation, then simply change MAX('tableX'[Date/Time]) to MIN('tableX'[Date/Time]). Like this:

enter image description here

To create the last column, you can use this:

Last Operation =
IF (
    'tableX'[Date/Time]
        = CALCULATE ( MAX ( 'tableX'[Date/Time] ), ALLEXCEPT ( 'tableX', tableX[ID] ) ),
    "Y",
    "N"
)

Upvotes: 1

Related Questions