Simplify dax formula and remove errors

Hey Anonymous ,

the error with the MAX expression is a little annoying. You can save the value of MAX in a variable, that should fix it.

For the many ifs I would use SWITCH with TRUE in combination instead:

https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/

So the formula could look like this:

Cumulated Sum =
VAR vMaxDate = YEAR(MAX(Abfrage1[fiscaldate]))

VAR vYear_1 = CALCULATE( [AverageBudgetYear], FILTER( ALL(Abfrage1[fiscaldate]), vMaxDate = 2020))
VAR vYear_2 = CALCULATE( [AverageBudgetYear], FILTER( ALL(Abfrage1[fiscaldate]), vMaxDate = 2021))
-- and so on

RETURN
SWITCH( TRUE(),
    vMaxDate = 2020, vYear_1,
    vMaxDate = 2021, vYear_2
-- and so on
)

I don't fully understand your approach. For me it looks like you use the MAX value for the calculate. Then you could just add it dynamically from the variable:

Cumulated Sum =
VAR vMaxDate = YEAR(MAX(Abfrage1[fiscaldate]))
VAR vResult = CALCULATE( [AverageBudgetYear], FILTER( ALL(Abfrage1[fiscaldate]), vMaxDate = vMaxDate ))
RETURN
    vResult