Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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()
))))))
Solved! Go to 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:
Proud to be a Super User!
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)
Formula(B)
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:
Proud to be a Super User!
This is what I was trying to write at the start!