topic Re: Newbie - Can this be Simplied please? in DAX Commands and Tips

Newbie - Can this be Simplied please?

Lucky71 — Wed, 08 Dec 2021 22:02:25 GMT

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

))))))

Re: Newbie - Can this be Simplied please?

ValtteriN — Wed, 08 Dec 2021 22:20:21 GMT

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!

Re: Newbie - Can this be Simplied please?

Lucky71 — Wed, 08 Dec 2021 22:51:42 GMT

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

)

Re: Newbie - Can this be Simplied please?

Lucky71 — Wed, 08 Dec 2021 23:01:51 GMT

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

))