Substract from Different Rows on a Table

[Anonymous]

Hi Team. Is there a way to reproduce this in Powerbi.?

This is the table I have to, in theory, I would like to subtract the time or duration between 1:11 AM and 1:04AM the same way I did in my excel file. Any idea on how to achieve this?

[Anonymous]

@v-alq-msft  Can you do the same but with the timestamp in a different order and different dates? I see that it works in yours but mine has different dates and times and does not show the same results.

[v-alq-msft]

Hi, @Anonymous 

I modified data to reproduce your scenario.

Table:

You may create an index column in Power Query and then modify the measure or column as below.

Calculated column:

Result Column = 
var tab = 
ADDCOLUMNS(
    ALL('Table'),
    "Start",[Status Start Date]+[Start Time],
    "End",[Status End Date]+[End Time]
)
var totalseconds =  
DATEDIFF(
    MAXX(
        FILTER(
            tab,
            [Index]=EARLIER('Table'[Index])-1
        ),
        [End]
    ),[Status Start Date]+[Start Time],SECOND
)
var m = 
COALESCE(INT(DIVIDE(totalseconds,60)),0)
var s = COALESCE(MOD(totalseconds,60),0)
return
m&":"&s

Measure:

Result Measure = 
var tab = 
ADDCOLUMNS(
    ALL('Table'),
    "Start",[Status Start Date]+[Start Time],
    "End",[Status End Date]+[End Time]
)
var totalseconds =  
DATEDIFF(
    MAXX(
        FILTER(
            tab,
            [Index]=MAX('Table'[Index])-1
        ),
        [End]
    ),MAX([Status Start Date])+MAX([Start Time]),SECOND
)
var m = 
COALESCE(INT(DIVIDE(totalseconds,60)),0)
var s = COALESCE(MOD(totalseconds,60),0)
return
m&":"&s

Best Regards

Allan

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

First, thank you so much for the hand. Im still getting the following miscalculation. I think you are close but not quite getting the correct calculation or formatting. Your table is still organized even though it shows a gap between 5:17 and 6:31 pm. Can you try with the table having unsorted date times with 2 different users?

Again, appreciate the time.

[v-alq-msft]

Hi, @Anonymous 

Based on your descripton, I created data to reproduce your sceanrio. The pbix file is attached in the end.

Table:

You may create an index column in Power Query and then create a measure or a column as below.

Measure:

Result Measure = 
var totalseconds =  
DATEDIFF(
    CALCULATE(
        MAX('Table'[Status End Datetime]),
        FILTER(
            ALL('Table'),
            [Index]=MAX('Table'[Index])-1
        )
    ),MAX('Table'[Status Start Datetime]),SECOND
)
var m = 
COALESCE(INT(DIVIDE(totalseconds,60)),0)
var s = COALESCE(MOD(totalseconds,60),0)
return
m&":"&s

Calculated column:

Result Column = 
var totalseconds =  
DATEDIFF(
    CALCULATE(
        MAX('Table'[Status End Datetime]),
        FILTER(
            ALL('Table'),
            [Index]=EARLIER('Table'[Index])-1
        )
    ),[Status Start Datetime],SECOND
)
var m = 
COALESCE(INT(DIVIDE(totalseconds,60)),0)
var s = COALESCE(MOD(totalseconds,60),0)
return
m&":"&s

Result:

Best Regards

Allan

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

[Anonymous]

@Fowmy the Var only captures the Measures, is there a way to use the columns?

[Fowmy]

@Anonymous 

You need to first add an index column in Power Query then add the following DAX column to the table:

Time Diff = 

var __currtime = Table2[Start Time]
var __previoustime  =  MAXX( FILTER( Table2 , Table2[Index] =  EARLIER(Table2[Index]) - 1), Table2[End Time]) 
var _Diff = DATEDIFF(+__previoustime , __currtime, SECOND )
return
_Diff
    

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn