Occup%

[ChandraDXB]

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.

[ChandraDXB]

Dear Ibendlin,

Thanks for your efforts.

The final outcome will be the below:

Thanks in advance.

[lbendlin]

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]

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.

[lbendlin · Accepted solution]

see attached for a modified version

[ChandraDXB]

Million thanks for a perfect solution.

[lbendlin]

here's a first stab at it with the general idea.  Still need to figure out the correct formula though .