slow measure needs help!

[tonysharpe]

Hi there,

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