Occupancy ratio

[ChandraDXB]

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

[ChandraDXB]

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

[Anonymous]

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.

[ChandraDXB]

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: