Forum Discussion
Create Measure and Line Chart for Future Occupancy
Hello,
I would like to create a line chart that will show how much a facility will be occupied (or was occupied) for any given date. My x-axis would be every day of the year with the values being a percent-occupancy. To get the percent occupancy, it's simply a calculation between the total rooms available and the total rooms occupied. Here's some sample data:
So the total number of rooms here is 4 but if we look at today's date (8/9/22), then we'd see that this building is only 25% occupied since only one room (Room 1) is occupied today. If we were to fast forward to 8/15, we'd see the occpancy would be 50% because the start date for Room 2 overlaps with Room 1. That all being said, I need to create a measure that can do that math for each day of the week, and then plot it on a line chart.
Any help is greatly appreciated!
Thank you!
Hi, JLambs20
You can try the following methods.
Date Table:
Date = CALENDAR(MIN('Table'[Start]),MAX('Table'[End]))Measure:
Measure = VAR N1 = CALCULATE ( COUNT ( 'Table'[Room] ), FILTER ( ALL ( 'Table' ), [Start] <= SELECTEDVALUE ( 'Date'[Date] ) && [End] >= SELECTEDVALUE ( 'Date'[Date] ) ) ) VAR N2 = CALCULATE ( COUNT ( 'Table'[Room] ) ) RETURN DIVIDE ( N1, N2 )Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
JLambs20 , expand your table in power query like this
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2
have one table with all dates and room
DAX new table = crossjoin(Distinct(Table[room]) , calendar(Min(Table[Start Date]), Max(Table[end Date]) ))
Have common room and date table
Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte]))
room = Distinct(Table[room])
you can analyze measure with these two common table joined to both tables
divide(countrows(Table), countrows([new DAX Table]) )
Hi amitchandak, I'm following you up to a point. I have expanded my query and also created the new DAX table. However, in the next part you say "have common room and date table". Is that a 3rd table? Or am I adding these new columns to the DAX table that was created? I do already have a date table in my model (not sure that matters at this point). Anyway, I'd appreciate some clarity! Thank you.
Hi, JLambs20
You can try the following methods.
Date Table:
Date = CALENDAR(MIN('Table'[Start]),MAX('Table'[End]))Measure:
Measure = VAR N1 = CALCULATE ( COUNT ( 'Table'[Room] ), FILTER ( ALL ( 'Table' ), [Start] <= SELECTEDVALUE ( 'Date'[Date] ) && [End] >= SELECTEDVALUE ( 'Date'[Date] ) ) ) VAR N2 = CALCULATE ( COUNT ( 'Table'[Room] ) ) RETURN DIVIDE ( N1, N2 )Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
