Newbie - Can this be Simplied please?

[Lucky71]

Can the following be made genric please?  The formula works, but Im sure there must be an easier way!

The formula returns
1) zero for Financial Years:  20,21&22
2) For Year 23+. 
    23:  22Base + '23 Incremental (23v22)'
    24:  22Base  + 23 + '24 Incremental (24v23)'
    25:  22Base  + 24 + '25 Incremental (25v24)'

etc

DAX Formula:

5YP ARR FY23Plus =

Var BASEYR = CALCULATE(FactData2[ARR],Dates[FY]="FY22")

Var IncFY23 = CALCULATE(FactData[mACV YoY Total (Recurring)],Dates[FY]="FY23")

Var IncFY24 = IncFY23 + CALCULATE(FactData[mACV YoY Total (Recurring)],Dates[FY]="FY24")

Var IncFY25 = IncFY24 + CALCULATE(FactData[mACV YoY Total (Recurring)],Dates[FY]="FY25")

Var IncFY26 = IncFY25 + CALCULATE(FactData[mACV YoY Total (Recurring)],Dates[FY]="FY26")

Var IncFY27 = IncFY26 + CALCULATE(FactData[mACV YoY Total (Recurring)],Dates[FY]="FY27")

RETURN

if(SELECTEDVALUE(Dates[FY]) IN {"FY20", "FY21","FY22"} , 0 ,

    BASEYR +

    if(SELECTEDVALUE(Dates[FY])="FY23",IncFY23,

    if(SELECTEDVALUE(Dates[FY])="FY24",IncFY24,

    if(SELECTEDVALUE(Dates[FY])="FY25",IncFY25,

    if(SELECTEDVALUE(Dates[FY])="FY26",IncFY26,

    if(SELECTEDVALUE(Dates[FY])="FY27",IncFY27,

    blank()

))))))

[Lucky71]

Hi

THANK YOU very much for replying... 😊

I had already read that post, but just could not quite get my formula to work, your post helped a lot.  The formula(B) below now works.

I tried to write the formula without using varaibles earlier, but kept getting the following error:
"The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."
Any idea why this is not working please?  I really dont understand this.
Formula(A)

mACV YoY CTotal (Recurring) = if(SELECTEDVALUE(Dates[FY]) in {"FY21","FY22"},blank(),

CALCULATE(cMeasures_Mkt_ACV[mACV YoY Total (Recurring)],

all(Dates[Date]),

max(Dates[Date])>=date(2022,03,31)))

Formula(B)

5YP ARR FY23Plus v2 =

Var BASE = CALCULATE(FACT_ARR[actARR],Dates[FY]="FY22")

VAR MaxDate = MAX ('Dates'[Date] )

Var MinDate = date(2022,03,31)

RETURN

if(SELECTEDVALUE(Dates[FY]) IN {"FY20", "FY21","FY22"} , 0 ,

BASE +

CALCULATE(

cMeasures_Mkt_ACV[mACV YoY Total (Recurring)],

Dates[Date] > MinDate,

Dates[Date] <= MaxDate

)

)

[ValtteriN · Accepted solution]

Hi Lucky,

Based on your post it seems you are trying to calculate running total starting from year 2023?
I recommend reading this blog by SQLBI: "https://www.sqlbi.com/articles/computing-running-totals-in-dax/" 
I think it will be useful to your case.

Anyway you can modify their base calculation by adding a min date and then converting it to match your needs:

Sales RT :=

VAR MaxDate = MAX ( 'Date'[Date] ) -- Saves the last visible date

Var MinDate = date(2022,1,1)

RETURN

    CALCULATE (

        [Sales Amount],           -- Computes sales amount

'Date'[Date]>=MinDate, //add something like this

        'Date'[Date] <= MaxDate,  -- Where date is before the last visible date

        ALL ( Date )              -- Removes any other filters from Date

    )

Hopefully this helps and if it does consider accepting this as a solution!

[Lucky71]

This is what I was trying to write at the start!

mACV YoY CTotal (Recurring) = if(SELECTEDVALUE(Dates[FY]) in {"FY21","FY22"},blank(),

CALCULATE(cMeasures_Mkt_ACV[mACV YoY Total (Recurring)],

all(Dates[Date]),

Dates[Date]>date(2022,03,31),

Dates[Date]<=max(Dates[Date])

))