Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Substract from Different Rows on a Table

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
v-alq-msft
Community Support
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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.