Help Optimizing Consecutive Count Measure

[ct_tm]

Hello,

I have been working on creating a way to count the consecutive periods (Months for this specific measure) where Occupancy % of our properties is below 95%. I started with code that was inspired by a video from SQLBI: Counting consecutive days with sales – Unplugged #47 - SQLBI

This measure worked great, and was pretty fast but it does not work at the total level because it runs off the difference in Index between the reference point and the nearest hole. This index is a column value that corresponds to a single property, so when a table or matrix tries to total the value it just outputs a repeat of the final value in the table.

So, I worked on a dynamic solution that works at all levels and I was able to do it, but it is very slow (16 seconds).

Can anyone please look at this and help me find a way to optimize the code?
I am attaching a table of reference data containing the pieces of the equation because my model is just too large to make a sample of. Table names will need adjusted since they all exist in one place, but the solution needs to assume original table names as the data is not actually stored/related this way.

Sample Table Data File 

Consecutive Months Below Occupancy Threshold = 
VAR filtOcc = 
CALCULATETABLE(
    SUMMARIZE(
        'Calendar',
        'Calendar'[CurMonthOffset],
        "Occ%",
        CALCULATE(
            [Occupancy % No Management Date Filter],
            ALLSELECTED('General Community Information'[PropertyHMY])
        )
    ),
    ALL('Calendar')
)
VAR filtNoOcc =
    CALCULATETABLE(
        VALUES('Calendar'[CurMonthOffset]),
        FILTER(filtOcc, [Occ%] < .95 && NOT([Occ%]) = BLANK()),
        All('Calendar')
    )
VAR Ref = MAX('Calendar'[CurMonthOffset])
VAR AllRef = ALL('Calendar'[CurMonthOffset])
VAR CountNoOcc = EXCEPT(AllRef, filtNoOcc)
VAR NearestHole = 
    MAXX(
        FILTER(
            CountNoOcc,
            [CurMonthOffset] <= Ref
        ),
        'Calendar'[CurMonthOffset]
    )
VAR Result = Ref - NearestHole
RETURN
Result

[technolog]

The measure you've provided is trying to count the consecutive months where the Occupancy % is below 95%. The main issue you're facing is that the measure is slow, especially at the total level.

The measure you've written is quite complex, and the multiple table scans (CALCULATETABLE, FILTER, etc.) are likely the cause of the performance issue. Let's try to simplify and optimize it.

First, let's understand the logic:

filtOcc is creating a table that summarizes the Occupancy % for each month.
filtNoOcc is filtering out months where Occupancy % is above 95% or is blank.
Ref is getting the latest month in the current context.
AllRef is getting all months.
CountNoOcc is getting all months that are not in filtNoOcc.
NearestHole is finding the nearest month (from the current context) that has an occupancy above 95% or is blank.
Finally, Result is calculating the difference between the current month and the NearestHole.
Now, let's try to simplify:

Instead of creating multiple tables, we can try to work with a single table and iterate over it. We can use the EARLIER function to compare values within the same table.

Here's a more streamlined version:

Consecutive Months Below Occupancy Threshold =
VAR CurrentMonth = MAX('Calendar'[CurMonthOffset])
VAR ConsecutiveCount =
COUNTROWS(
FILTER(
ALL('Calendar'),
'Calendar'[CurMonthOffset] <= CurrentMonth &&
CALCULATE(
[Occupancy % No Management Date Filter],
ALLSELECTED('General Community Information'[PropertyHMY])
) < 0.95
)
) -
COUNTROWS(
FILTER(
ALL('Calendar'),
'Calendar'[CurMonthOffset] <= CurrentMonth &&
CALCULATE(
[Occupancy % No Management Date Filter],
ALLSELECTED('General Community Information'[PropertyHMY])
) >= 0.95
)
)
RETURN
ConsecutiveCount
This version first counts all the months below 95% occupancy up to the current month and then subtracts the count of months above 95% occupancy up to the current month. The difference gives us the consecutive months below 95%.

This should be faster because it reduces the number of table scans and calculations. However, the real performance will depend on the size and complexity of your data model. Always test any new measure in your environment to ensure it meets your performance and accuracy requirements.