Indexing rows by a distinct values and then getting the difference from 2 other columns.

[Anonymous]

Looking to index rows by the distinct value in column #Order. Then Getting the difference of the 2nd value(2:25:48) from column #First time and the 1st value(1:47:31) from column #Last time. That way Im only getting the duration between last time and first time of each unique order. 

Would it be easier to transpose all times for each distinct order on a new query? or is it possible using Dax? 

[Icey · Accepted solution]

Hi @Anonymous ,

Please check if this is what you want:

Create a column like so:

Column = 
VAR Index_1 = 'Table'[Index] - 1
VAR CurrentOrder =
    CALCULATE (
        MAX ( 'Table'[Order] ),
        FILTER ( 'Table', 'Table'[Index] = Index_1 + 1 )
    )
VAR LastOrder =
    CALCULATE (
        MAX ( 'Table'[Order] ),
        FILTER ( 'Table', 'Table'[Index] = Index_1 )
    )
VAR FirstTime = 'Table'[First Time]
VAR LastTime =
    IF (
        CurrentOrder = LastOrder,
        MAXX ( FILTER ( 'Table', 'Table'[Index] = Index_1 ), [Last Time] )
    )
VAR DateDiffSeconds =
    DATEDIFF ( LastTime, FirstTime, SECOND )
VAR Hours =
    TRUNC ( DateDiffSeconds / 3600 )
VAR Minutes =
    TRUNC ( ( DateDiffSeconds - Hours * 3600 ) / 60 )
VAR Seconds =
    MOD ( DateDiffSeconds - Hours * 3600, 60 )
VAR Result =
    IF (
        LastTime <> BLANK (),
        FORMAT ( Hours, "00" ) & ":"
            & FORMAT ( Minutes, "00" ) & ":"
            & FORMAT ( Seconds, "00" )
    )
RETURN
    Result

For more details, please check the attached PBIX file.

Best Regards,

Icey

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

[Anonymous]

Thanks @Icey. This is basically what I ended up doing.

I wanted to accomplish this On the query side, but grouping and then cluster indexing and then merging caused relatively long load times.

Thanks again

[ImkeF]

Hi @Anonymous 

if you run into performance problems with the DAX-solution, you could combine these 2 solutions in Power Query:

https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/

You'd have to apply this function on Order-level: https://www.youtube.com/watch?v=-3KFZaYImEY

(create Index-column AND apply the function from the blogpost)

[amitchandak]

If the columns are already detected  as time then try following

datediff(table[first_time],
maxx(filter(table,table[order] =earlier([order]) && table[first_time] <earlier(table[first_time])),table[first_time]),
Second)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin