Recursive Year-over-Year Calculation in Power BI DAX

[menphis21]

Hello everyone,

I'm working on a Power BI report and I'm facing a challenge with a recursive calculation for adjusted revenues. I need to calculate the adjusted revenue for the current year based on the previous year's adjusted revenue, factoring in a growth rate percentage that can be dynamically adjusted by the user through a slicer.

Here's the scenario:

• I have a table named Global with a column [Expenses] representing the total revenues for each year.
• There's a slicer allowing the user to adjust the growth rate percentage, which is represented by the measure [% Growth].
• I want to calculate the adjusted revenues such that for years up to and including 2024, it simply takes the sum of [Expenses]. For years after 2024, it should take the previous year's adjusted revenue and multiply it by (1 + [% Growth]).

The challenge is to reference the adjusted revenue of the previous year in a measure, which seems to require a recursive calculation. Here's the DAX formula I've been trying to use:
AdjustedRevenues =
VAR defvalue = SUM('Global'[Expenses])
RETURN
IF(
MAX('Years filter'[Year]) <= 2024,
CALCULATE(defvalue),
CALCULATE([AdjustedRevenues], DATEADD('Years filter'[Year], -1, YEAR)) * (1 + [% growth])
)

% growth = (sum('Global'[growth])*100+[AdjustementvalueRevenues% Value])/100

However, I'm aware that DAX does not support direct recursion. I'm looking for a way to achieve this year-over-year calculation without recursion. How can I structure my DAX formula or data model to calculate the adjusted revenues correctly?

Any help or guidance would be greatly appreciated!

[AlexisOlson]

This is an easy case. You just need to multiply by (1 + [%growth]) as many times as there are years beyond 2024.

Somthing like this:

AdjustedRevenues = 
VAR _LastKnownYear = 2024
VAR _Year = YEAR ( MAX ( 'Years filter'[Date] ) )
VAR _Revenue = SUM ( 'Global'[Total Revenues] )
VAR _LastKnownRevenue =
    CALCULATE ( 
        SUM ( 'Global'[Total Revenues] ),
        ALL ( 'Years filter' ),
        'Years filter'[Year] = _LastKnownYear
    )
VAR _FutureYears = _Year - _LastKnownYear
VAR _Growth = POWER ( 1 + [AdjustementvalueRevenues% Value] / 100, _FutureYears )
VAR _Result =
    IF (
        _FutureYears <= 0,
        _Revenue,
        _LastKnownRevenue * _Growth
    )
RETURN
    _Result

See attached.

[menphis21]

Hi @AlexisOlson ,

Thank you for your time.

Thats not exactly what i am looking for. I want to be able, from 2024, to take from the initial value of GDP Growth, and add to it, a percent.
for example, if i add 1% in my GDP Growth, i will have this below, and calculate as shown in the excel

GDP Growth	GDP Growth adjusted
0	0
0.047596827	0.0475968
0.057535264	0.0575353
-0.005054405	-0.005054
0.116065759	0.1160658
0.115058794	0.1150588
0.059303776	0.0593038
0.04	0.05
0.05	0.06
0.08	0.09
0.054	0.064
0.054	0.064
0.054	0.064
0.054	0.064

[AlexisOlson · Accepted solution]

Like this?

AdjustedRevenues = 
VAR _Adj = [AdjustementvalueRevenues% Value] / 100
VAR _LastKnownYear = 2024
VAR _Year = YEAR ( MAX ( 'Years filter'[Date] ) )
VAR _Revenue = SUM ( 'Global'[Total Revenues] )
VAR _LastKnownRevenue =
    CALCULATE ( 
        SUM ( 'Global'[Total Revenues] ),
        ALL ( 'Years filter' ),
        'Years filter'[Year] = _LastKnownYear
    )
VAR _FutureYears_ =
    CALCULATETABLE (
        SUMMARIZE ( 'Global', 'Global'[Year], 'Global'[GDP Growth] ),
        ALL ( 'Global' ),
        'Years filter'[Year] > _LastKnownYear && 'Years filter'[Year] <= _Year
    )
VAR _Growth =
    PRODUCTX (
        _FutureYears_,
        1 + 'Global'[GDP Growth] + _Adj
    )
VAR _Result =
    IF (
        _Year <= _LastKnownYear,
        _Revenue,
        _LastKnownRevenue * _Growth
    )
RETURN
    _Result

The basic principle is the same.

[menphis21]

Hi @AlexisOlson ,
You are a genius ! big thank you 🙂

i have just add the scenario ( i have a filter in my dashboard)

VAR _Scenario = SELECTEDVALUE ( 'Global'[Scenario] )

VAR _FutureYears_ =

    CALCULATETABLE (

        SUMMARIZE ( 'Global', 'Global'[Year],'Global'[Scenario], 'Global'[גידול בתוצר] ),

        ALL ( 'Global' ),

        'Years filter'[Year] > _LastKnownYear && 'Years filter'[Year] <= _Year,

        'Global'[Scenario] = _Scenario 

    )

One more time, thank you !

[menphis21]

@AlexisOlson one more time, thank you for your precious help.
I'm encountering other difficulties regarding the impact of parameters. I have a parameter that adjusts the defense budget, which in turn affects the deficit and consequently the public debt.
So, how can i calculate the public debt which it depend on some mesures that we calculate before?
i cant use the same method as i understand

Additionally, the interest rate is dependent, so I end up with circular references and can't find a way out.

Here is the link with the updated Excel file and PBI report, if it can help.

How can i use the same method for a value that is a measure (deficit_) ?

Thank you again for your efforts

https://jumbomail.me/j/2ovVWw6NdUqHH8p

[AlexisOlson]

The debt is the last known debt minus the cumulative sum of land sales and deficit for each projected year.

PublicDebt = 
VAR _LastKnownYear = 2024
VAR _Year = YEAR ( MAX ( 'Years filter'[Date] ) )
VAR _Debt = SUM ( 'Global'[Public debt] )
VAR _LastKnownDebt =
    CALCULATE ( 
        SUM ( 'Global'[Public debt] ),
        ALL ( 'Years filter' ),
        'Years filter'[Year] = _LastKnownYear
    )
VAR _FutureYears_ =
    FILTER (
        ALL ( 'Years filter' ),
        'Years filter'[Year] > _LastKnownYear && 'Years filter'[Year] <= _Year
    )
VAR _CumulativeDebt =
    SUMX (
        _FutureYears_,
        0.004 * [Adjusted Nominal GDP] + [Deficit_]
    )
VAR _Result =
    IF (
        _Year <= _LastKnownYear,
        _Debt,
        _LastKnownDebt - _CumulativeDebt
    )
RETURN
    _Result

[menphis21]

Hi @AlexisOlson 

Thank you for your time and your precious solution.
there is something that i dont understand.
why for calculate the cumulative debt, you are multiplying the Adjusted nominal GDP by 0.04 + deficit ?
That works, no doubt haha but i would be happy to understand.
as you said  in the beginning, "The debt is the last known debt minus the cumulative sum of land sales and deficit for each projected year."

if i want to use the same definition from excel, how would i do it ?
Thank you 

[AlexisOlson]

I just used the definitions from Excel. It has

Land Sales = (Nominal GDP - Before Tax) * 0.4%

and

Public debt = previous public debt - Land Sales - Deficit

[menphis21]

Alexis, thank you very much.

I think I have finished all the calculations and dynamic slicers, and it would not have been possible without your help.

I need to get used to these approach, and understand well well each variable calculated in each measure.

thank you

[menphis21]

hi @AlexisOlson ,
hope you are doing well.
i am sorry to one more time disturb. I succeed to calculate all the rest of calculations
but i noticed that the interest is a variable that is included on the total expenses and is calculated from the expenses interest and expenses interest is depending on the public debt and debt interest rate and public debt is depending on defecit and it causes me a circular reference.
Its easier to understand in the excel file:
https://jumbomail.me/j/srtaD_HQ7Emt4p2

thank you one more time for your precious help

[AlexisOlson]

I'm not sure how to help here. I don't really know how the logic of your model is supposed to work, so I don't know how to resolve circular dependencies in the Excel model. I was able to help turn an Excel model into Power BI measures by converting recursive logic into cumulative logic but if the recursive logic is broken, I don't have a solid foundation to start from.

[menphis21]

Thank you.
I will try to simplify.
when i drag the formula in the 2024, it gave me the same result.
So lets say that the expenses interest, its the prior year public multiplied by the debt interest

there is a way to calculate it ?

https://www.jumbomail.me/l/en/gallery/694B6F3845536E3859446E464B3342347436653838673D3D/1235813566

[menphis21]

Hi @AlexisOlson,

sorry to bother. Any idea ?

Thanks

[AlexisOlson]

This has gotten pretty far from the original question and I don't remember all the details. Can you write a new question summarizing what you've got so far and what part you're still stuck on?

Please include sample data / attachments, formulas, and expected results as described here:
How to Get Your Question Answered Quickly - Microsoft Fabric Community

[menphis21]

HI @AlexisOlson 

Thank you, sorry I was off for a few days.
of course I'm going to recap.
The interest rate is part of the Total expenses. Starting from 2025, the interest rate corresponds to Interest expenses.
The interest expenses are calculated by taking the prior public debt multiplied by the debt interest rate.
The public debt is calculated by taking the prior public debt minus the deficit minus land sales. The deficit is revenues minus expenses.
And expenses should contains this "calculated" interest rates and that creates a circular loop in Power BI (in the measure Expenses).

If you have any guidance, I'm all ears. Thank you again for your help I am attaching the excel file and power bi files
https://jumbomail.me/j/t9MJgZ436UqXLvQ

[Greg_Deckler]

@menphis21 Well, if it is truly recursive then there's not much you can do. However, there are pseudo-recursive problems, recursive problems that you don't have to solve via recursion. Would need sample data to test with and see if yours is one of those. Here is a video I did on psuedo-recursive problems. @AlexisOlson and @tamerj1 have also done amazing work on this front.

[tamerj1]

Thank you @Greg_Deckler for the tag. 

Hi @menphis21

When you say "For years after 2024, it should take the previous year's adjusted revenue and multiply it by (1 + [% Growth])." Do you mean the % Growth of 2024? Or do you have Growth forecast data for future years?

Please provide sample PBIX file

[menphis21]

Hi @tamerj1 , Thank you.
yes it should take the previous year's adjusted revenue.
i am joining the pbix file and also the excel where you will see exactly the formula (but in excel its simple)
https://jumbomail.me/j/7Syf_0w1xkG_o3J

Thank you for your help 🙂