Measure taking too long

ppm1  Anonymous  This is an accounting visual that we are trying to build. All the measures are working but only that it is taking far too long as from the measure on tax. In summary, this is what I did:

1. I have a fact table coming from an odata, which contains actual figures. Measure to read Actuals below

Board01aTabPLGL = min(TabPLGLItem[Calc Type])

Board01P&LAllTransactionAmountwithSign = sumx(GLChartOfAccounts,[P&LAllTransactionAmountEUR]*GLChartOfAccounts[Sign])

Board02P&LRunningTotal = if(
     HASONEFILTER(TabPLGLItem[GLItem])
     ,
     CALCULATE(
             [Board01P&LAllTransactionAmountwithSign]
             ,
             ALL(TabPLGLItem[GLItem])
             ,
             TabPLGLItem[Index] <= VALUES(TabPLGLItem[Index])
             )
     ,
     blank()
)

Board03P&LActualAmount = SWITCH(
	[HeaderCalcType]
	,
	BLANK(),BLANK()
	,
	1,[Board01P&LAllTransactionAmountwithSign]
	,
	2,[Board02P&LRunningTotal]
)

2. I have another fact table coming from PowerPivot through Direct Query, which contains budget figures. The DAX code for this is exactly the same as Actuals, except for the naming

3. I then created a P&L containing 2 columns, 1 for Actual & 1 for Budget in 1 matrix visual

4. I now want to create a third column named Actualized Budget. This will add all actual amounts before and upto the date selected and add all budget amounts after the date selected. Measure is given below:

Board10aFirstDateSelected = FIRSTDATE(BudgetCalendar[Date])

Board10aLastDateSelected = LASTDATE(BudgetCalendar[Date])

Board10c1AllTransactionAmountwithSignActualBudgetActualized = 

VAR SeparatorDate = if(and(TODAY()>[Board10aFirstDateSelected], TODAY()> [Board10aLastDateSelected]),[Board10aLastDateSelected],TODAY())

VAR ActualizedActual = CALCULATE([Board01P&LAllTransactionAmountwithSign],filter(BudgetCalendar,BudgetCalendar[Date]<=SeparatorDate))

VAR ActualizedBudget = CALCULATE([AmountWithSign],filter(BudgetCalendar,BudgetCalendar[Date]>SeparatorDate))

RETURN
    ActualizedActual+ActualizedBudget

5. I now need to calculate the actualized Tax based on the actualized budget. The tax formula is that you do not pay any tax up and until your cumulative net losses have not been wiped out; so you need to calculate net profit before tax for both the current financial year and cumulative of all financial years where there is net loss before tax

Net Profit/Loss for current financial year Measures:

Board10d1TaxPeriodActualBudgetActualized = 
VAR Result = 
CALCULATE(
    [Board10c1AllTransactionAmountwithSignActualBudgetActualized],FILTER(all(GLAccountClientUser[GLAccountValue]),GLAccountClientUser[GLAccountValue]<4700000))
RETURN
    Result

Board10d2TaxPeriodRunningTotalActualBudgetActualized = if(
     HASONEFILTER(TabPLGLItem[GLItem])
     ,
     CALCULATE(
             [Board10d1TaxPeriodActualBudgetActualized]
             ,
             ALL(TabPLGLItem[GLItem])
             ,
             TabPLGLItem[Index] < VALUES(TabPLGLItem[Index])
             )
     ,
     blank()
)

Net Profit/Loss for cumulative financial years Measures:

Board10d3TaxCumulativeActualBudgetActualized = 
VAR Result =
CALCULATE([Board10c1AllTransactionAmountwithSignActualBudgetActualized],FILTER(ALL(BudgetCalendar[Date]),BudgetCalendar[Date] >= date(2021,10,1) && BudgetCalendar[Date] <= MAX(BudgetCalendar[Date])),filter(all(GLAccountClientUser[GLAccountValue]),GLAccountClientUser[GLAccountValue]<4700000))
RETURN
    Result

Board10d4TaxCumulativeRunningTotalActualBudgetActualized = if(
     HASONEFILTER(TabPLGLItem[GLItem])
     ,
     CALCULATE(
             [Board10d3TaxCumulativeActualBudgetActualized]
             ,
             ALL(TabPLGLItem[GLItem])
             ,
             TabPLGLItem[Index] < VALUES(TabPLGLItem[Index])
             )
     ,
     blank()
)

Tax Calculation on current financial year Measure:

Board10d5TaxCalcPeriodActualBudgetActualized = 
VAR Result = 
calculate(if([Board10d2TaxPeriodRunningTotalActualBudgetActualized]<0,0,[Board10d2TaxPeriodRunningTotalActualBudgetActualized]*[CorporateTax]))
RETURN
Result

Tax Calculation on cumulative financial years Measure:

Board10d6TaxCalcCumulativeActualBudgetActualized = 
VAR Result = 
calculate(if([Board10d4TaxCumulativeRunningTotalActualBudgetActualized]<0,0,[Board10d4TaxCumulativeRunningTotalActualBudgetActualized]*[CorporateTax]))
RETURN
    Result

6. Measure to get actualized Tax applicable amount in the P&L:

Board10d71TaxAmountWithSignActualBudgetActualized = 
VAR Result = [Board10c1AllTransactionAmountwithSignActualBudgetActualized]
VAR TaxApplicable =
    -1
        * MIN (
            [Board10d5TaxCalcPeriodActualBudgetActualized],
            [Board10d6TaxCalcCumulativeActualBudgetActualized]
        )
RETURN
    IF ( SELECTEDVALUE ( TabPLGLItem[GLItem] ) = "Tax", TaxApplicable, Result )

Measure to summarize above measure:

Board10d81PLTaxAmountWithSignActualBudgetActualized = 
VAR
Result = SUMX (
    SUMMARIZE (TabPLGLItem,[GLItem]),
    CALCULATE (
        [Board10d71TaxAmountWithSignActualBudgetActualized]
    )
)
RETURN
Result

7. Running Total to get Net Profit after Tax

Board11RunningTotalActualBudgetActualized = 
if(
     HASONEFILTER(TabPLGLItem[GLItem])
     ,
     CALCULATE(
             [Board10d81PLTaxAmountWithSignActualBudgetActualized]
             ,
             ALL(TabPLGLItem[GLItem])
             ,
             TabPLGLItem[Index] <= VALUES(TabPLGLItem[Index])
             )
     ,
     blank()
)

8. Lastly, Measure to combine the running totals for net profit with amounts for P&L items:

Board12ProjectedAmountActualBudgetActualized = 
SWITCH(
	[HeaderCalcType]
	,
	BLANK(),BLANK()
	,
	1,[Board10d81PLTaxAmountWithSignActualBudgetActualized]
	,
	2,[Board11RunningTotalActualBudgetActualized]
)

Snapshot of visual after point 4 above. It is after this that performance gets very heavy:

Unfortunately, I cannot share the file, hence such a long post. Hopefully, you or someone out there can make this a bit lighter for better performance 😅