Running totals are taking too long and the visual exceeds time limit

[CarlSagan]

I have a visual that uses five measures which compute running totals based on dates. I was wondering if anyone could give me tips on optimizing the performance my code, I'm basically doing the same thing for each measure. Choose a specific date as the last date to sum up all previous totals under.

Beginning Balance = 
VAR lastD = LASTDATE(Assets[process_period_date])
var lastM = MONTH(lastD)
VAR prevM = PREVIOUSMONTH(lastD)

var begBal = CALCULATE(
                SUM(Assets[amount]),
                Assets[process_period_date] <= prevM
)

RETURN begBal

Then I there is the final measure that sums up the previous five measure, I'm not sure if it is computing all of the measures again?

Last Ending Balance = 
    [Beginning Balance] + [Last Earnings] + [Last Expenses] + [Last Distribution] + [Last Contributions] + [Last Transfer Total]

Am I supposed to be reusing variables so I don't need to compute them again?

[AnthonyGenovese]

What are the other measure definitions? I have an optimization idea but would need to see the other ones.

Another thing you can do is run each measure by itself, record times, and see which one is taking the longest. Then work on optimizing that one particular measure. 

If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!

[CarlSagan]

They are all pretty much the same

Last Contributions = 

VAR mostRecentMonth = LASTDATE(Assets[process_period_date])
VAR prevMonth = PREVIOUSMONTH(mostRecentMonth)
var result = CALCULATE(
                SUM(Assets[amount]),
                Assets[tran_code] = 101 ||
                Assets[tran_code] = 102 ||
                Assets[tran_code] = 103 ||
                Assets[tran_code] = 109 ||
                Assets[tran_code] = 133,
                Assets[process_period_date] = mostRecentMonth
)
RETURN result

2.

Last Distribution = 
VAR mostRecentMonth = LASTDATE(Assets[process_period_date])
VAR distributions = CALCULATE(
                        SUM(Assets[amount]),
                        Assets[tran_code] = 501 ||
                        Assets[tran_code] = 502 ||
                        Assets[tran_code] = 503 ||
                        Assets[tran_code] = 504 ||
                        Assets[tran_code] = 509
)
RETURN distributions

3.

Last Earnings = 
VAR mostRecentMonth = LASTDATE(Assets[process_period_date])

VAR earnings = CALCULATE(
                    SUM(Assets[amount]),
                    Assets[tran_code] = 301 ||
                    Assets[tran_code] = 302 ||
                    Assets[tran_code] = 305 ||
                    Assets[tran_code] = 309 ||
                    Assets[tran_code] = 333,
                    Assets[process_period_date] = mostRecentMonth
)
RETURN earnings

4.

Last Expenses = 
VAR mostRecentMonth = LASTDATE(Assets[process_period_date])

VAR lastExpense = CALCULATE(
                    SUM(Assets[amount]),
                    Assets[tran_code] = 401 ||
                    Assets[tran_code] = 402 ||
                    Assets[tran_code] = 403 ||
                    Assets[tran_code] = 404 ||
                    Assets[tran_code] = 405 ||
                    Assets[tran_code] = 406 ||
                    Assets[tran_code] = 407 ||
                    Assets[tran_code] = 408 ||
                    Assets[tran_code] = 409 ||
                    Assets[tran_code] = 410 ||
                    Assets[tran_code] = 411 ||
                    Assets[tran_code] = 433,
                    Assets[process_period_date] = mostRecentMonth
)
RETURN lastExpense

5.

Last Transfer Total = 
VAR mostRecentMonth = LASTDATE(Assets[process_period_date])
VAR transferIn = CALCULATE(
                    SUM(Assets[amount]),
                    Assets[tran_code] = 201 ||
                    Assets[tran_code] = 209,
                    Assets[process_period_date] = mostRecentMonth
)
VAR transferOut = CALCULATE(
                    SUM(Assets[amount]),
                    Assets[tran_code] = 601 ||
                    Assets[tran_code] = 609,
                    Assets[process_period_date] = mostRecentMonth
)

return transferIn + transferOut

[AnthonyGenovese]

Why not just do this? You should be able to put all your codes in an IN statement, or a long OR block. That will reduce some overhead and potential blocking. Also, if you have a dimension for asset transaction codes and have a realtionship to the asset table, you could create an attribute such as [Tran Code Type] "Transfer IN", "Transfer Out" etc and then just filter on those. 

CALCULATE(
                    SUM(Assets[amount]),
                    Assets[tran_code] = { 401,402,ALL YOUR CODES}
                    Assets[process_period_date] = mostRecentMonth
)

Also, is there a filter on Assets[Process_period_date]? Is the measure displaying data correctly? Because I would think you would need to remove the filter before doing the = mostRecentMonth

If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!

[CarlSagan]

I don't think using the IN operator is going to make that much of a difference since the IN is translated into the multiple OR expression anyway. So I re-created the data model in a different project and am doing the same exact calculations and I have it working.

The more I've been playing around with it, it seems to be that the bottle neck is actually bringing in related columns from different tables. I think, how can I tell using the optimizer?