Slow measure needs help!

DAX DAX Calculations
1

Dear All,

I am an accountant and just started with DAX and power bi/pivot. Currently I am working on a small dataset (44K rows) and it’s a trial balance file. It has 4 years of data by month. Half is from a manual excel file for balance sheet balances and the other half is actual GL transactions in SQL table. Therefore, I need to calculate starting balance and use cumulative GL transactions to add the starting balance to compute current balance. I do have a few dimension tables (depts, grouping, period etc.). And I am trying to recreate a manual file my team maintains and it shows all monthly TB balances by account, dept, mapping etc. It will have approximately 9K rows * 50 columns if I show everything in excel. Period Index 40 is my cut off number between manual TB balance and SQL GL transaction balance. Below is my code and I know it’s far from good DAX, but considering my fact table is only 44K rows, it shouldn’t be this slow in my mind. Currently if I just show account balances with 2 dimensions (dept, grouping), it’s still very fast. When I add more dimensions, it’s insanely slow. I guess showing 9K rows is not good and my STARTING_BALANCE measure needs to rewrite.

[1.Total] is just sum of balances. On and before my cut-off month, it’s the correct balance sheet balance already. After that, I did a rolling sum of GL transactions. For balance sheet, it’s rolling balance of all periods + starting balance. For P&L, it’s rolling sum of current year only + starting balance.

Can someone please share insights what might be the issue and how I should fix this?

Really appreciate all the help!

=
VAR STARTING_BALANCE = CALCULATE( [1. Total], Period[Index] = 40, all(Period) )
VAR CURRENT_BALANCE =IF (
MIN ( Acct[CATE] ) = “BS”,
CALCULATE (
[1. Total],
FILTER (
ALL ( Period ),
Period[Index] <= MAX ( Period[Index] )
&& Period[Index] >= 41
)
)
,
CALCULATE (
[1. Total],
FILTER (
ALL ( Period ),
Period[Index] <= MAX ( Period[Index] )
&& Period[Year_Name] = MAX ( Period[Year_Name] )
&& Period[Index] >= 41
)
)
) + STARTING_BALANCE

VAR RESULT = SWITCH ( [Calc_Divider_Value] , 1, [1. Total], 2, [1. Total], 3, CURRENT_BALANCE)
RETURN RESULT

2

Can you try something like this

=
VAR STARTING_BALANCE = CALCULATE( [1. Total], Period[Index] = 40, ALL(Period) )
VAR MaxIndex = MAX( Period[Index] )
VAR MaxYearName = MAX( Period[Year_Name] )

VAR CURRENT_BALANCE =
    CALCULATE (
        [1. Total],
        FILTER (
            ALL ( Period ),
            Period[Index] <= MaxIndex
            && Period[Index] >= 41
            && (
                MIN ( Acct[CATE] ) <> “BS” 
                || ( MIN ( Acct[CATE] ) = “BS” && Period[Year_Name] = MaxYearName )
            )
        )
    ) + STARTING_BALANCE

VAR RESULT = 
    SWITCH (
        [Calc_Divider_Value],
        1, [1. Total],
        2, [1. Total],
        3, CURRENT_BALANCE,
        BLANK()   // Default case
    )

RETURN RESULT