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). I...
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.