Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
User | Count |
---|---|
25 | |
12 | |
8 | |
7 | |
7 |
User | Count |
---|---|
29 | |
13 | |
13 | |
10 | |
6 |