Ending balance calculation speed up

Hi 193 

You can use the following code:

Measure = CALCULATE([BS],FILTER(ALL(GL),GL[Acc]=MAX(GL[Acc])&&[Index]<=MAX(GL[Index])))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

The filter on the high granularity index column is the reason for the slowness. If the running total won't change with slicers, etc., it would be good to pre-calculate it in your query. The new WINDOW function may be a good approach too. However, below is a measure expression that is much faster. Please try it and report back. It calculates the running total from a day standpoint and then just subtracts any index values that came later on same day (if any). Note that I also used the Date column from your Date table in the visual, instead of the one from the GL table.

End_bal 2 = 
var thisindex = max(GL[Index])
var maxdate = MAX('Date'[Date])
VAR IndexValues = CALCULATETABLE(DISTINCT(GL[Index]), ALL(GL), VALUES(GL[Acc]), 'Date'[Date] = maxdate)
var FilteredIndexValues = FILTER(IndexValues, GL[Index] > thisindex)
VAR DayRT =
    CALCULATE (
        [BS],
        ALL(gl), VALUES(GL[Acc]),
        REMOVEFILTERS('Date'),
        'Date'[Date] <= maxdate
        
    )
VAR Subtract =
    CALCULATE (
        [BS],
        ALL(gl), VALUES(GL[Acc]),
        GL[Index] in FilteredIndexValues
        
    )
RETURN
   DayRT - Subtract

Pat