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

[bdeleur]

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

[v-cgao-msft · Accepted solution]

Hi @bdeleur ,

Please try these:

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

[v-cgao-msft · Accepted solution]

Hi @bdeleur ,

Please try these:

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

[bdeleur]

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

[v-cgao-msft · Accepted solution]

Hi @bdeleur , @Anonymous ,

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

[bdeleur]

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

[Anonymous]

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?