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