daily utilization rate per program

[bcoro]

I tried to created a table in power query that addresses program enrollments in range. I'm going to try to use an example that will make more sense. Hotels stays are an easier explaination.

Let's say I have a hotel that holds 50 people max.

• Each check in has an ID associated with it for every person who checks in
• Associated with that ID is a check in date and a check out date (some check outs may be null)

In theory, I would like to be able to create a measure that will aggregate a daily average each month/quarter/year and so on. 

My attempt:

I tried to created a separate date table that addressed each checkin and if it is in-range of each date in the reporting period January 1, 2021 to December 31, 2023. Obviously people come and go so I currenlty have over 2 million rows in this calendar. My issue is handeling those with null values because it has created a check out date of December 31, 1899. I don't really think this is the best apporach or if it's better to create some sort of parameter for a reporting period that can filter measurements based on the Calendar date being selected, and the check in and check out date. 

In the example below:

If I were looking at 1/13/2020 there would be 4 people in the hotel. The date out doesn't count as a stay as the person is leaving that day, however, If the in and out fall on the same day, it should still count as a one night stay, they just happened to leave early or it was a mistake.

Check In ID	Date In	Date Out
1	1/1/2020	1/14/2020
2	1/5/2020
3	1/12/2020	1/17/2020
4	1/2/2020	1/15/2020
5	1/20/2020	1/20/2020

[v-hashadapu]

Hi @bcoro , I hope you're doing well! Just checking in to see if you had a chance to review the details shared earlier. If any of the information addressed your needs, feel free to mark it as "Accept as Solution" or give it a 'Kudos' to help others in the community. Please let me know if you have any further questions!

[v-hashadapu]

Hi @bcoro ,
I hope the information shared by @Ashish_Excel was helpful. If you have any additional questions or would like to explore the topic further, feel free to reach out. If any of the responses resolved your issue, please mark it "Accept as solution" and give it a 'Kudos' to support other members in the community.
Thank you!

[v-hashadapu]

Hi @bcoro ,
I wanted to follow up and see if you’ve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!

[Ashish_Excel]

Hi,

Download PBI file from here.

Hope this helps.

[ThxAlot]

[bcoro]

I'm not able to filter the date with my other columns to check that its counting correctly. Or to categorize by the hotels daily average. 

[ryan_mayu · Accepted solution]

@bcoro 

you can try this 

create a column

date out 2 = if('Table'[Date In ]='Table'[Date Out],'Table'[Date Out]+1,'Table'[Date Out])

 

then in the date table create another column

Column =

var _date='date'[Date]

return CALCULATE(COUNTROWS('Table'),'Table'[Date In ]<=_date&&('Table'[date out 2]>_date||ISBLANK('Table'[date out 2])))

pls see the attachment below