Subtract different columns from previous row

[Anonymous]

I am new to Power BI and know very little M code, any and all help would be greatly appreciated.

I am trying to get the "Gap" time from when an assignment ended and the next assignment started.

So Logically I would take DT_TM_START (tran_num 54) - DT_TM_End (tran_num 53) or said another way 10:34:11-10:27:38

Thanks for all the help in advance

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

You can also achieve it by an easier DAX code, here's my solution.

1.Create a measure.

Time Gap =
VAR _NowStart =
    MAX ( 'Table'[DT_TM_START] )
VAR _LastEnd =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[TRAN_NUM] = MAX ( 'Table'[TRAN_NUM] ) - 1 ),
        'Table'[DT_TM_END]
    )
RETURN
    CONVERT ( IF ( _LastEnd = BLANK (), 0, _NowStart - _LastEnd ), DATETIME )

 2.Change the format of the measure to hh:nn:ss

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[SteveHailey]

Hi @Anonymous. You can do this without needing to know any M code. It can be done through the GUI. I created a .pbix file linked here that you can download if it's helpful. Let me know if you get hung up on any of these steps and I'll be happy to walk you through it in more detail.

Here are the steps:

1. Add an Index column from 0  (Add column > Index column > From 0)

2. Add an Index column from 1 (Add column > Index column > From 1)

3. Do a Merge Queries, joining the table with itself, from the Index 0 column to the Index 1 column. This will allow you to access the previous row. (Home > Merge Queries)

4. Expand the column to bring in the "DT_TM_END" column from the prior row.

5. Sort the TRAN_NUM column ascending. The sort will get messed up during the merge so this just puts it back in the original order.

6. Add a custom column to do the subtraction.

7. Remove unneeded columns and rename them as you see fit.