Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi
I want to calculate the number of HH:MM between 2 columns - both have the same reference however the inbound has 1 and outbound has 0 and same reg too.
e.g.
Bound Reg Time Ref
1 ABC 03/02/2023 10:00 142536
0 ABC 03/02/2023 12:30 142536
The difference for reg: ABC or Ref 142536 should be 02:30 (HH:MM)
Thanks.
Solved! Go to Solution.
hi @AI14
Not sure if i fully get you, supposing your data is like:
you can plot a table with the ref column and a measure like:
Duration =
VAR _reg = MAX(TableName[Reg])
VAR _time1 =
MAXX(
FILTER(
ALL(TableName),
TableName[Reg] = _reg
),
TableName[Time]
)
VAR _time2 =
MINX(
FILTER(
ALL(TableName),
TableName[Reg] = _reg
),
TableName[Time]
)
RETURN
FORMAT(_time2 - _time1, "h:mm")
it worked like:
hi @AI14
Not sure if i fully get you, supposing your data is like:
you can plot a table with the ref column and a measure like:
Duration =
VAR _reg = MAX(TableName[Reg])
VAR _time1 =
MAXX(
FILTER(
ALL(TableName),
TableName[Reg] = _reg
),
TableName[Time]
)
VAR _time2 =
MINX(
FILTER(
ALL(TableName),
TableName[Reg] = _reg
),
TableName[Time]
)
RETURN
FORMAT(_time2 - _time1, "h:mm")
it worked like:
Thank you it worked!
Hi @AI14
Where are you planning to show the result? Are you looking for a measure?
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
to show on a table and a chart
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
14 | |
14 | |
12 | |
9 |