Optimizing running total with a reset measure

Hi Anonymous 

I happened to come across your post. I have had a play around and have attached a PBIX with some potential improvements.

 

The main things I looked at:

1. Minimise nested measures (some overhead involved):
   • Rewrote [Qty on Hand Total] using SUMX.
   • Eliminated [Pos to Neg] and [No of Days (Internal)] from the final measure.
2. Rather than using ALL ( 'Calendar'[Period Date] ), use a smaller date range covering global min/max Stock[Date].
3. Rewrote the logic a bit to detect when the count should start. See AllRequiredDatesFlag variable.
4. Rather than counting dates in a table containing a contiguous range of dates as in original _DatesToUse
   variable, take the difference between date bounds & add 1.

A matrix with just Period Date & Working (IMPROVED) Measure now takes a bit under 2 seconds for me, or a bit over 3 seconds with Qty Projected as well.

 

The relevant updated measures are:

Qty On Hand Total = 
SUMX (
    Stock,
    Stock[Qty On Hand] + Stock[Qty Supply] - Stock[Qty Demand]
)

Qty Projected = 
VAR MinStockDate =
    CALCULATE ( MIN ( Stock[Date] ), REMOVEFILTERS ( ) )
RETURN
    CALCULATE ( 
        [Qty On Hand Total],
        DATESBETWEEN ( 'Calendar'[Period Date], MinStockDate, MAX ( 'Calendar'[Period Date] ) )
    )

Working (IMPROVED) Measure = 
VAR _RefDate = MAX ( Calendar[Period Date] )
VAR MinStockDate =
    CALCULATE ( MIN ( Stock[Date] ), REMOVEFILTERS ( ) )
VAR MaxStockDate =
    CALCULATE ( MAX ( Stock[Date] ), REMOVEFILTERS ( ) )
VAR AllRequiredDates =
    DATESBETWEEN (
        Calendar[Period Date],
        MinStockDate,
        MaxStockDate
    )
VAR AllRequiredDatesFlag =
    ADDCOLUMNS ( AllRequiredDates, "@Pos", [Qty Projected] >= 0 )
VAR RelevantDatesFlag =
    FILTER (
        AllRequiredDatesFlag,
        Calendar[Period Date] <= _RefDate
    )
VAR LastPositive =
    MAXX (
        FILTER ( RelevantDatesFlag, [@Pos] ),
        Calendar[Period Date]
    )
VAR LastNegative =
    MAXX (
        FILTER ( RelevantDatesFlag, NOT [@Pos] ),
        Calendar[Period Date]
    )
RETURN
    IF (
        LastNegative = _RefDate,
        INT ( LastNegative - LastPositive )
    )

There's bound to be room for improvement, but hopefully this is of some use.

 

Regards,

Owen