topic Re: Recursive Year-over-Year Calculation in Power BI DAX in DAX Commands and Tips

Recursive Year-over-Year Calculation in Power BI DAX

menphis21 — Thu, 22 Aug 2024 12:29:45 GMT

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!

Re: Recursive Year-over-Year Calculation in Power BI DAX

Greg_Deckler — Thu, 22 Aug 2024 13:33:31 GMT

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.

Re: Recursive Year-over-Year Calculation in Power BI DAX

tamerj1 — Thu, 22 Aug 2024 16:16:22 GMT

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

Re: Recursive Year-over-Year Calculation in Power BI DAX

menphis21 — Thu, 22 Aug 2024 18:20:37 GMT

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 🙂

Re: Recursive Year-over-Year Calculation in Power BI DAX

AlexisOlson — Thu, 22 Aug 2024 18:58:48 GMT

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.

Re: Recursive Year-over-Year Calculation in Power BI DAX

menphis21 — Thu, 22 Aug 2024 20:32:47 GMT

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

Re: Recursive Year-over-Year Calculation in Power BI DAX

AlexisOlson — Thu, 22 Aug 2024 21:30:02 GMT

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.

Re: Recursive Year-over-Year Calculation in Power BI DAX

menphis21 — Sat, 24 Aug 2024 20:03:02 GMT

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 !

Re: Recursive Year-over-Year Calculation in Power BI DAX

menphis21 — Sun, 25 Aug 2024 14:55:27 GMT

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

Re: Recursive Year-over-Year Calculation in Power BI DAX

AlexisOlson — Mon, 26 Aug 2024 15:54:43 GMT

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

Re: Recursive Year-over-Year Calculation in Power BI DAX

menphis21 — Mon, 26 Aug 2024 20:34:28 GMT

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

Re: Recursive Year-over-Year Calculation in Power BI DAX

AlexisOlson — Mon, 26 Aug 2024 20:48:08 GMT

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

Re: Recursive Year-over-Year Calculation in Power BI DAX

menphis21 — Tue, 27 Aug 2024 14:31:47 GMT

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

Re: Recursive Year-over-Year Calculation in Power BI DAX

menphis21 — Wed, 04 Sep 2024 08:50:25 GMT

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

Re: Recursive Year-over-Year Calculation in Power BI DAX

AlexisOlson — Wed, 04 Sep 2024 17:39:14 GMT

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.

Re: Recursive Year-over-Year Calculation in Power BI DAX

menphis21 — Wed, 04 Sep 2024 21:59:04 GMT

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

Re: Recursive Year-over-Year Calculation in Power BI DAX

menphis21 — Sun, 08 Sep 2024 07:24:57 GMT

Hi AlexisOlson,

sorry to bother. Any idea ?

Thanks

Re: Recursive Year-over-Year Calculation in Power BI DAX

AlexisOlson — Mon, 09 Sep 2024 15:24:08 GMT

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

Re: Recursive Year-over-Year Calculation in Power BI DAX

menphis21 — Mon, 23 Sep 2024 12:54:41 GMT

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