Search code examples
excelpowerbidaxpowerpivot

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

Solution

  • 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"
)