Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
@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.
Hi, @carlosdajer
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.
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.
Hi, @carlosdajer
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.
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
86 | |
83 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |