cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AI14
Helper III
Helper III

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

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.

1 ACCEPTED SOLUTION
FreemanZ
Community Champion
Community Champion

hi @AI14 

Not sure if i fully get you, supposing your data is like:

FreemanZ_0-1675437813669.png

 

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:

FreemanZ_1-1675437870657.png

 

View solution in original post

4 REPLIES 4
FreemanZ
Community Champion
Community Champion

hi @AI14 

Not sure if i fully get you, supposing your data is like:

FreemanZ_0-1675437813669.png

 

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:

FreemanZ_1-1675437870657.png

 

Thank you it worked!

AlB
Super User
Super User

Hi @AI14 

Where are you planning to show the result? Are you looking for a measure?

 

SU18_powerbi_badge

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors