Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Lucky71
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
ValtteriN
Super User
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]>=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!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Lucky71
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
)
)
ValtteriN
Super User
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]>=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!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors