Calculating occupied beds during month in slicer

[Anonymous]

Hi everyone,

I was hoping someone would be able to help me with something that I'm stuck on, please.

We're a care home provider and one of the KPIs we would like to track is the number of days residents stayed in the home (let's call it ResDays) but I'm new to PowerBI and at a bit of a loss with how to calculate it. 

The report I'm building has 3 tables that I think are relevant to this question - DateTable, cv_BI_Occupancy and cv_facilities. They are laid out as below:

cv_facilities:

name	Address
Care Home A	123 XYZ
Care Home B	456 ABC
Care Home C	789 DEF

DateTable (dates run from 1 Jan 2020 to 31 Dec 2021):

Date	MonthYear	MonthYearNo	Financial Year
01 January 2020	Jan 2020	202001	2020
02 January 2020	Jan 2020	202001	2020
03 January 2020	Jan 2020	202001	2020
04 January 2020	Jan 2020	202001	2020
05 January 2020	Jan 2020	202001	2020

cv_BI_Occupancy (the system generates a leave date of 31 December 2999 for any rooms that are currently occupied):

Site	RoomNo	TypeOfStay	Service	Arrival	LeaveDate
Care Home A	114	Permanent	AL	21 September 2012	01 February 2020
Care Home A	216	Permanent	DEM	16 December 2019	18 January 2020
Care Home A	117	Permanent	AL	23 March 2019	02 January 2020
Care Home A	214	Permanent	DEM	13 October 2019	16 March 2020
Care Home A	G01	Permanent	AL	16 April 2015	31 December 2999
Care Home A	114	Permanent	AL	29 September 2020	31 December 2999
Care Home A	207	Permanent	AL	30 December 2019	31 December 2999
Care Home B	215	Permanent	AL	30 January 2020	25 August 2020
Care Home B	111	Permanent	DEM	13 October 2014	08 October 2020
Care Home B	228	Permanent	AL	19 June 2020	17 December 2020
Care Home B	130	Permanent	DEM	03 May 2013	30 November 2020
Care Home B	304	Permanent	AL	17 January 2019	31 December 2999
Care Home B	404	Permanent	AL	06 December 2018	31 December 2999
Care Home B	302	Permanent	AL	13 February 2020	31 December 2999
Care Home C	118	Permanent	AL	08 December 2019	06 November 2020
Care Home C	112	Permanent	AL	18 September 2013	09 May 2020
Care Home C	120	Permanent	AL	18 March 2020	25 December 2020
Care Home C	113	Permanent	AL	15 February 2012	08 January 2020
Care Home C	003	Permanent	AL	01 September 2020	31 December 2999
Care Home C	310	Permanent	DEM	22 October 2020	31 December 2999
Care Home C	227	Permanent	AL	15 June 2020	31 December 2999

The relationships set up are as follows:

DateTable[Date] to cv_BI_Occupancy[Leave Date] - 1 to many

cv_facilities[name] to cv_BI_Occupancy[Site] - 1 to many

The report I am trying to create will be for one care home at any given time, and for one period at any given time so there are two slicers synced to all pages, those being DateTable[MonthYear] and cv_facilities[name]. It will show financial and non-financial information so this ResDays calculation only forms a small but important part of the overall report.

I've replicated in Excel what I would expect my outcomes to be for two random periods, as well as how that would be made up from the table above:

	Resident Days in July	Resident Days in November
Care Home A	62	90
Care Home B	217	150
Care Home C	93	126

Site	RoomNo	TypeOfStay	Service	Arrival	LeaveDate	Jul 2020	Nov 2020
Care Home A	114	Permanent	AL	21 September 2012	01 February 2020	0	0
Care Home A	216	Permanent	DEM	16 December 2019	18 January 2020	0	0
Care Home A	117	Permanent	AL	23 March 2019	02 January 2020	0	0
Care Home A	214	Permanent	DEM	13 October 2019	16 March 2020	0	0
Care Home A	G01	Permanent	AL	16 April 2015	31 December 2999	31	30
Care Home A	116	Permanent	AL	29 September 2020	31 December 2999	0	30
Care Home A	207	Permanent	AL	30 December 2019	31 December 2999	31	30
Care Home B	215	Permanent	AL	30 January 2020	25 August 2020	31	0
Care Home B	111	Permanent	DEM	13 October 2014	08 October 2020	31	0
Care Home B	228	Permanent	AL	19 June 2020	17 December 2020	31	30
Care Home B	130	Permanent	DEM	03 May 2013	30 November 2020	31	30
Care Home B	304	Permanent	AL	17 January 2019	31 December 2999	31	30
Care Home B	404	Permanent	AL	06 December 2018	31 December 2999	31	30
Care Home B	302	Permanent	AL	13 February 2020	31 December 2999	31	30
Care Home C	118	Permanent	AL	08 December 2019	06 November 2020	31	6
Care Home C	112	Permanent	AL	18 September 2013	09 May 2020	0	0
Care Home C	120	Permanent	AL	18 March 2020	25 December 2020	31	30
Care Home C	113	Permanent	AL	15 February 2012	08 January 2020	0	0
Care Home C	003	Permanent	AL	01 September 2020	31 December 2999	0	30
Care Home C	310	Permanent	DEM	22 October 2020	31 December 2999	0	30
Care Home C	227	Permanent	AL	15 June 2020	31 December 2999	31	30

I can create a column in the cv_BI_Occupancy table with a calculation that gives me the right figures if I hard code in a start and end date like this:

RESIDENT DAYS =

VAR STARTDATE = DATE(2020,07,01)

VAR ENDDATE = DATE(2020,07,31)

RETURN

IF(cv_BI_Occupancy[Arrival].[Date] > ENDDATE, 0,

IF(cv_BI_Occupancy[LeaveDate] < STARTDATE, 0,

VALUE(DATEDIFF( MAX( STARTDATE, cv_BI_Occupancy[Arrival].[Date]), MIN( cv_BI_Occupancy[LeaveDate], ENDDATE),DAY))))

But this becomes obsolete as soon as the period changes in the slicer so I'd like to reference a start and end date using the slicer if at all possible? I'm also sure that creating a column is not the best way to do it so is there a more efficient method? 

Any help at all would be gratefully received, thank you!

[Anonymous]

Hi all,

Thank you for the help and the example files! I'll have a look over the next couple of days and come back to confirm it's working as expected. 

Happy new year!

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

To my knowledge, you could delete the relationhip between Date table and cv_BI_Occupancy table so that you could use the DateTable[Date] for Slicer. 

Then use the following formula to creata measure:

1. in cv_BI_Occupancy table

RESIDENT DAYS =
VAR _STARTDATE =
    MIN ( 'Date'[Date] )
VAR _ENDDATE =
    MAX ( 'Date'[Date] )
RETURN
    IF (
        MAX ( 'cv_BI_Occupancy'[Arrival] ) > _ENDDATE,
        0,
        IF (
            MAX ( 'cv_BI_Occupancy'[LeaveDate] ) < _STARTDATE,
            0,
            VALUE (
                DATEDIFF (
                    MAX ( MAX ( 'cv_BI_Occupancy'[Arrival] ), _STARTDATE ),
                    MIN ( MAX ( cv_BI_Occupancy[LeaveDate] ), _ENDDATE ),
                    DAY
                )
            )
        )
    )

2. in cv_facilities table

Measure =
SUMX ( 'cv_BI_Occupancy', [RESIDENT DAYS] )

The final output is shown below:

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-eqin-msft I hope you don't mind me asking a follow up question here.

If I was to try and use the same method to calculate unit days instead of resident days I would need to omit some duplicate rows. Imagine 2 people sharing a room for the full length of a 30 day period, the measure at the moment would calculate 60 days occupied as each person would have their own entry in the system, which would be perfect for my original query, however if I wanted to know how many days that specific room was occupied, I would want the solution to only be 30 days.

Here's an example:

Site	RoomNo	TypeOfStay	Service	Arrival	LeaveDate	Jul 2020	Nov 2020
Care Home A	114	Permanent	AL	21 September 2019	01 October 2020	31	0
Care Home A	114	Permanent	AL	21 September 2019	01 January 2021	0	30

I was thinking I'd be able to copy the measures and just adjust a couple of things, so is this an easy amendment to make or is there a completely new solution that's needed?

Also, if it's easier I'm more than happy to create a new post rather than reopen an old and solved problem.

Thanks in advance!

[Anonymous]

Thanks Evelyn - that works perfectly. I was clearly being too stubborn with trying to keep the slicer format! 

[CongPham30Shine]

Exactly the way I'm doing. And as I know, maybe it's the only way.

[CongPham30Shine]

Hi @Anonymous , can u send me the PBIX file, I could give you some help. Because I got the same problems before.

[Ashish_Mathur]

Hi,

Would you want to analyse data only as far as a month?  i.e. would you ever want to get an answer to a particular date level such as occupancy as on July 26, 2020?  Also, would you be OK if in the back-end i treat 31 December 2999 as today's date? 

[Anonymous]

Thanks both.

Ashish, for this KPI it would only be for the full month and only ever for one month at a time. 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Johanno]

Hi, this is probably not a final solution but maybe some help on the way - a measure that counts the days that are within the selected month of the slicer:

Resident days selected month = 
VAR StartSelectedMonth = DATE(2020, SELECTEDVALUE(Dates[Month]), 1)
VAR EndSelectedMonth = DATE(2020, SELECTEDVALUE(Dates[Month])+1, 1) - 1
VAR Arrival = CALCULATE( MAX(cv_BI_Occupancy[Arrival]), ALL(Dates[Date])) //ignore the slicer
VAR Leave = CALCULATE( MAX(cv_BI_Occupancy[LeaveDate]), ALL(Dates[Date])) //ignore the slicer
VAR FilteredDateTable = FILTER(VALUES(Dates[Date]), 
                            Dates[Date] >= StartSelectedMonth && 
                            Dates[Date] <= EndSelectedMonth)
VAR Days = SUMX( FilteredDateTable, IF( Dates[Date] >= Arrival && Dates[Date] <= Leave, 1, 0))
RETURN Days

In general you should use a measure instead of calculated column whenever possible since it's calculated when needed instead of using memory. If it's a small model and if it solves your problem it won't be an issue.