cancel
Showing results for
Did you mean: Frequent Visitor

## Newbie - Can this be Simplied please?

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()

))))))

1 ACCEPTED SOLUTION  Super User

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] <= 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!

Proud to be a Super User!

3 REPLIES 3 Frequent Visitor

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
)
)  Super User

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] <= 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!

Proud to be a Super User! Frequent Visitor

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])
))  