Create Measure and Line Chart for Future Occupancy

[JLambs20]

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!

[v-zhangti · Accepted solution]

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.

[JLambs20]

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. 

[amitchandak]

@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]) )

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...