Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
bdeleur
Helper III
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.

Tnx already for the help

 

2 ACCEPTED SOLUTIONS
v-cgao-msft
Community Support
Community Support

Hi @bdeleur ,

Please try these:

 

 

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

 

 

vcgaomsft_0-1671160054496.png

A table:

 

 

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

 

 

vcgaomsft_1-1671160105911.png

Or a measure:

 

 

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

 

 

vcgaomsft_2-1671160161444.png

You can add a visual filter like:

vcgaomsft_3-1671160240102.png

 

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

View solution in original post

Hi @bdeleur , @bdeleur2 ,

 

Please try this:

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

View solution in original post

5 REPLIES 5
v-cgao-msft
Community Support
Community Support

Hi @bdeleur ,

Please try these:

 

 

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

 

 

vcgaomsft_0-1671160054496.png

A table:

 

 

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

 

 

vcgaomsft_1-1671160105911.png

Or a measure:

 

 

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

 

 

vcgaomsft_2-1671160161444.png

You can add a visual filter like:

vcgaomsft_3-1671160240102.png

 

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

@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?

Hi @bdeleur , @bdeleur2 ,

 

Please try this:

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

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

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?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.