Forum Discussion

ChandraDXB's avatar
ChandraDXB
Frequent Visitor
2 years ago

Occupancy ratio

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

 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      ChandraDXB
      Frequent Visitor
      2 years ago

      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:

       

  • ChandraDXB's avatar
    ChandraDXB
    Frequent Visitor
    2 years ago

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