Occupancy ratio

ChandraDXB — Tue, 02 Jan 2024 17:50:01 GMT

Dear All Fowmy ,

Need your help in calculating meausres for occupancy ratios

1. Current month end vs same month previous year, assume 30.11.2023 vs 30.11.2022

2. YTD this year vs YTD previous year, assume 30.11.2023

3. Running 12 months vs Running 12 months previous year, assume 30.11.2023

I have given here two set of data sets, one table provides information about units occupied and second table provides information about units available.

Table 1

Table 2 - units available

Please help.

Thanks in advance

Re: Occupancy ratio

Anonymous — Wed, 03 Jan 2024 09:36:29 GMT

Hi ChandraDXB

You can create several measures as follow.

Q1 2022 = VAR _D1 = DATE ( 2022, 11, 1 ) VAR _D2 = DATE ( 2022, 11, 30) RETURN IF ( SELECTEDVALUE ( Table1[Start Date] ) >= _D1 && SELECTEDVALUE ( Table1[Start Date] ) <= _D2, DIVIDE ( DATEDIFF ( _D2, SELECTEDVALUE ( Table1[Start Date] ), DAY ), DATEDIFF ( _D2, _D1, DAY ) ), IF ( SELECTEDVALUE ( Table1[End Date] ) >= _D1 && SELECTEDVALUE ( Table1[End Date] ) <= _D2, DIVIDE ( DATEDIFF ( SELECTEDVALUE ( Table1[End Date] ), _D1, DAY ), DATEDIFF ( _D2, _D1, DAY ) ), 0 ) )

Q1 2023 = VAR _D1 = DATE ( 2023, 11, 1 ) VAR _D2 = DATE ( 2023, 11, 30) RETURN IF ( SELECTEDVALUE ( Table1[Start Date] ) >= _D1 && SELECTEDVALUE ( Table1[Start Date] ) <= _D2, DIVIDE ( DATEDIFF ( _D2, SELECTEDVALUE ( Table1[Start Date] ), DAY ), DATEDIFF ( _D2, _D1, DAY ) ), IF ( SELECTEDVALUE ( Table1[End Date] ) >= _D1 && SELECTEDVALUE ( Table1[End Date] ) <= _D2, DIVIDE ( DATEDIFF ( SELECTEDVALUE ( Table1[End Date] ), _D1, DAY ), DATEDIFF ( _D2, _D1, DAY ) ), 0 ) )

Q2 2022 = VAR _D1 = DATE ( 2022, 1, 1 ) VAR _D2 = DATE ( 2022, 11, 30) RETURN IF ( SELECTEDVALUE ( Table1[Start Date] ) >= _D1 && SELECTEDVALUE ( Table1[Start Date] ) <= _D2, DIVIDE ( DATEDIFF ( _D2, SELECTEDVALUE ( Table1[Start Date] ), DAY ), DATEDIFF ( _D2, _D1, DAY ) ), IF ( SELECTEDVALUE ( Table1[End Date] ) >= _D1 && SELECTEDVALUE ( Table1[End Date] ) <= _D2, DIVIDE ( DATEDIFF ( SELECTEDVALUE ( Table1[End Date] ), _D1, DAY ), DATEDIFF ( _D2, _D1, DAY ) ), 0 ) )

Q2 2023 = VAR _D1 = DATE ( 2023, 1, 1 ) VAR _D2 = DATE ( 2023, 11, 30) RETURN IF ( SELECTEDVALUE ( Table1[Start Date] ) >= _D1 && SELECTEDVALUE ( Table1[Start Date] ) <= _D2, DIVIDE ( DATEDIFF ( _D2, SELECTEDVALUE ( Table1[Start Date] ), DAY ), DATEDIFF ( _D2, _D1, DAY ) ), IF ( SELECTEDVALUE ( Table1[End Date] ) >= _D1 && SELECTEDVALUE ( Table1[End Date] ) <= _D2, DIVIDE ( DATEDIFF ( SELECTEDVALUE ( Table1[End Date] ), _D1, DAY ), DATEDIFF ( _D2, _D1, DAY ) ), 0 ) )

Q3 2022 = VAR _D1 = DATE(2021, 12, 1) VAR _D2 = DATE(2022, 11, 30) RETURN IF ( SELECTEDVALUE ( Table1[Start Date] ) >= _D1 && SELECTEDVALUE ( Table1[Start Date] ) <= _D2, DIVIDE ( DATEDIFF ( _D2, SELECTEDVALUE ( Table1[Start Date] ), MONTH ), DATEDIFF ( _D2, _D1, MONTH ) ), IF ( SELECTEDVALUE ( Table1[End Date] ) >= _D1 && SELECTEDVALUE ( Table1[End Date] ) <= _D2, DIVIDE ( DATEDIFF ( SELECTEDVALUE ( Table1[End Date] ), _D1, MONTH ), DATEDIFF ( _D2, _D1, MONTH ) ), 0 ) )

Q3 2023 = VAR _D1 = DATE(2022, 12, 1) VAR _D2 = DATE(2023, 11, 30) RETURN IF ( SELECTEDVALUE ( Table1[Start Date] ) >= _D1 && SELECTEDVALUE ( Table1[Start Date] ) <= _D2, DIVIDE ( DATEDIFF ( _D2, SELECTEDVALUE ( Table1[Start Date] ), MONTH ), DATEDIFF ( _D2, _D1, MONTH ) ), IF ( SELECTEDVALUE ( Table1[End Date] ) >= _D1 && SELECTEDVALUE ( Table1[End Date] ) <= _D2, DIVIDE ( DATEDIFF ( SELECTEDVALUE ( Table1[End Date] ), _D1, MONTH ), DATEDIFF ( _D2, _D1, MONTH ) ), 0 ) )

Then change the Format of each measure to Percentage.

Is this the result you expect?

If I've misunderstood you, please provide detailed sample data and the results you are hoping for. We can better understand the problem and help you. Show it as a screenshot or excel. Please remove any sensitive data in advance.

Best Regards,
Community Support Team _Yuliax
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Occupancy ratio

ChandraDXB — Thu, 04 Jan 2024 16:19:53 GMT

Thanks for your help. I will share complete solution in spreadsheet.

Re: Occupancy ratio

ChandraDXB — Sat, 06 Jan 2024 18:28:15 GMT

https://1drv.ms/x/s!Amn-LF3-8-0ziScRrB84FSiZXdA0 the whole excel sheet is attached. It has the following information

1. Raw Data - Units occpied and Units available

2. Processed data using power query and copied and pasted in the sheet

3. PIVOT and final calcuation

4. We can use Date table to keep it internactive.

5. Need to do whole working using DAX

The final solution is highlighted below:

topic Occupancy ratio in DAX Commands and Tips

Occupancy ratio

Re: Occupancy ratio

Re: Occupancy ratio

Re: Occupancy ratio