Ending balance calculation speed up

[193]

Hello,

I need to calculate ending balance depending on filter selections in the report.  

I found a way to get the desired result but it takes too long...

I add pbx file here: https://1drv.ms/u/s!AjCQWBzIRUrlwVraSjUjhGqOpTri?e=vqDO3G 

Any ideas how could I improve my measure?

Thanks!

[Anonymous · Accepted solution]

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.

[ppm1 · Accepted solution]

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

[193]

Hi,

@ppm1 , @Anonymous 

Both solutions solved my problem. @ppm1 interesting idea with window function, but I'm not sure how to implement it. 

Either way now speed is acceptable!

Big thanks for you both!

[Ashish_Mathur]

There is no file there.

[Anonymous · Accepted solution]

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.