topic Re: date/time difference between 2 columns if they have the same reference in DAX Commands and Tips

date/time difference between 2 columns if they have the same reference

AI14 — Fri, 03 Feb 2023 14:52:02 GMT

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.

Re: date/time difference between 2 columns if they have the same reference

AlB — Fri, 03 Feb 2023 15:00:03 GMT

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.

Re: date/time difference between 2 columns if they have the same reference

AI14 — Fri, 03 Feb 2023 15:05:57 GMT

to show on a table and a chart

Re: date/time difference between 2 columns if they have the same reference

FreemanZ — Fri, 03 Feb 2023 15:24:48 GMT

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:

Re: date/time difference between 2 columns if they have the same reference

AI14 — Fri, 03 Feb 2023 15:41:02 GMT

Thank you it worked!