Forum Discussion

ChandraDXB's avatar
ChandraDXB
Frequent Visitor
2 years ago
Solved

Occup%

Dear All,

 

I am working on calcuating occupancy % for a property portfolio (dummy) consiting of 2 buildings (total 6 units). This % can be at portfolio level or for each builing (using slicers). In terms of time dimesion, occupancy % is for a partcular month or YTD or last 12 months running total and corresponding previous year data. 

 

I have shared here Table 1 which provides information about units occupied, Table 2 which provides information about units available and Table 3 provides information occupancy %. 

 

I will be using slicer for properties and year and month slicer to filter data.

 

Table 1 = Units occupied

 

 

Table 2 Units available

 

 

Table 3  solution - highlighted.

 

 

The source file is https://1drv.ms/x/s!Amn-LF3-8-0ziScRrB84FSiZXdA0

 

I understand we can use DAX to get occupancy % easily rather doing same work in power query.

 

Thanks in advance.

 

 

6 Replies

  • ChandraDXB's avatar
    ChandraDXB
    Frequent Visitor
    2 years ago

     

     

    Dear Ibendlin,

    Thanks for your efforts.

    The final outcome will be the below:

     

    Thanks in advance.

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      Ok, I figured out the correct formula for both availability and occupancy per unit, independent of date range.  You can take it from here and do your YoY and YTD calculations.

       

       

      Available =
VAR d =
    GENERATESERIES ( MIN ( Dates[Date] ), MAX ( Dates[Date] ) ) 
// get units
VAR a =
    SUMMARIZE ( Availability, [Unit No] ) 
// get combined date series for each unit and intersect with date range
VAR b =
    ADDCOLUMNS (
        a,
        "av",
            COUNTROWS (
                INTERSECT (
                    d,
                    VAR u = [Unit No]
                    RETURN
                        SELECTCOLUMNS (
                            GENERATE (
                                SUMMARIZE (
                                    FILTER ( Availability, [Unit No] = u ),
                                    [Available from],
                                    [Available to]
                                ),
                                GENERATESERIES ( [Available from], [Available to] )
                            ),
                            "Value", [Value]
                        )
                )
            )
    ) 
// return percentage
RETURN
    DIVIDE ( SUMX ( b, [av] ), COUNTROWS ( a ) * COUNTROWS ( d ), 0 )

       

      same idea for occupancy.

      • ChandraDXB's avatar
        ChandraDXB
        Frequent Visitor
        2 years ago

        Dear Ibendlin,

         

        Oops, we are not getting what we are loooking for.

         

        Step 1 - get total no of days available for a unit, aggregate this data at bldg level

        Step 2 - get total no of days occupied for a unit, aggregate this data at bldg level

        Step 3 - Units occupied/units avaiable - % occupanyc at bldg level

         

        this is what DAX has provdied.

        This is for 2022 (Jan to Dec)

         

        Thansk for your efforts.