cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Calculate the time between to dates with a value in another field

Hai,

I have a big file wich contains different uniek numbers of patients i a ward.

So 1 patient has different rows becaus with everyg change of bed or room there is a new row.

That row has a start and end date so you can calculate the time the patient spend there. But I want so see the total length of stay of that patient in te ward.

What wil be the code in powerbi?

 Reference start date end date Duration hour 12345 1-1-2022 10:00 1-1-2022 15:00 5,0 12345 1-1-2022 15:00 1-1-2022 16:00 1,0

So the sum of this length of stay must be 6,0.

When I know the total length of stay I can use it so calculate how many patients are longer then 24 hours or 48 hours in the ward.

I hope someone is able to help me.

2 ACCEPTED SOLUTIONS
Community Support

Hi @bdeleur ,

``Duration hour = DIVIDE( DATEDIFF('Table'[start date],'Table'[end date],SECOND), 3600)``

A table:

``````Table 2 =
SUMMARIZE(
'Table',
'Table'[Reference],
"Hours",
SUM('Table'[Duration hour])
)``````

Or a measure:

``Measure = SUM('Table'[Duration hour])``

You can add a visual filter like:

``````24 =
COUNTROWS (
FILTER (
SUMMARIZE (
'Table',
'Table'[Reference],
"Hours", SUM ( 'Table'[Duration hour] )
),
[Hours] >= 24
)
)``````
``````48 =
COUNTROWS (
FILTER (
SUMMARIZE (
'Table',
'Table'[Reference],
"Hours", SUM ( 'Table'[Duration hour] )
),
[Hours] >= 48
)
)``````

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Community Support

``````24-48 =
COUNTROWS (
FILTER (
SUMMARIZE (
'Table',
'Table'[Reference],
"Hours", SUM ( 'Table'[Duration hour] )
),
[Hours] > 24 && [Hours] < 48
)
) + 0``````

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

5 REPLIES 5
Community Support

Hi @bdeleur ,

``Duration hour = DIVIDE( DATEDIFF('Table'[start date],'Table'[end date],SECOND), 3600)``

A table:

``````Table 2 =
SUMMARIZE(
'Table',
'Table'[Reference],
"Hours",
SUM('Table'[Duration hour])
)``````

Or a measure:

``Measure = SUM('Table'[Duration hour])``

You can add a visual filter like:

``````24 =
COUNTROWS (
FILTER (
SUMMARIZE (
'Table',
'Table'[Reference],
"Hours", SUM ( 'Table'[Duration hour] )
),
[Hours] >= 24
)
)``````
``````48 =
COUNTROWS (
FILTER (
SUMMARIZE (
'Table',
'Table'[Reference],
"Hours", SUM ( 'Table'[Duration hour] )
),
[Hours] >= 48
)
)``````

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Helper III

@v-cgao-msft . So I can use my own account again. Very happy.

Is this possible?:

When I use it on the big data file I created an other measure <24. The 24 as you created then needs to be 24 - 48. and 48 stays >48. Is that also possible?

Community Support

``````24-48 =
COUNTROWS (
FILTER (
SUMMARIZE (
'Table',
'Table'[Reference],
"Hours", SUM ( 'Table'[Duration hour] )
),
[Hours] > 24 && [Hours] < 48
)
) + 0``````

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Helper III

Super. works perfect. Tnx for your assistence. I'm very happy.

New Member

Hi, I'm the same bdeleur as the topic starter but somehow I can't login. Everytime I'm redirected to the register page.

So that is delay number 1 for my repons. Number 2 is that is was very busy at work. Sorry for that.

I followed your steps. Tnx for that because I was vey easy to understand and to follow.

When I use it on the big data file I created an other measure <24. The 24 as you created then needs to be 24 - 48. and 48 stays >48. Is that also possible?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors