Recursion in DAX

[rigiyeh]

Hi team,

I have a DAX measure (not calculated column) that calculates monthly KPI, and needs to refer to it's previous months' result to get current month number.

It's like something below:

I already have a DAX measure to calculate X = x1, x2, x3, ... (for each month)

Current month X = CALCULATE(SUMX(MyTable, [X]))

Cumulative X = 
var ThisMonth = SELECTEDVALUE(MyCalendar[Month])
return CALCULATE(SUMX(MyTable, [Current month X]), All(MyCalendar), MyCalendar[Month] <= ThisMonth))

However, for Y, it's calculated based on

[Current month Y] = [Current month's cumulative X] - [Previous month's cumulative Y]

where [Previous month's cumulative Y] is the sum of [Current month Y] for every month until previous month

This is causing a circular dependency in my DAX measure:

Current month Y = 
CALCULATE(SUMX(MyTable, [Cumulative X] - [Cumulative Y (last month)]))

Cumulative Y (last month) = 
var ThisMonth = SELECTEDVALUE(MyCalendar[Month])
var LastMonth = EDATE(ThisMonth, -1)
return CALCULATE(SUMX(MyTable, [Current month Y]), All(MyCalendar), MyCalendar[Month] <= LastMonth))

I saw some post saying that DAX cannot do recursively self-reference, and I also cannot write table to store the previous month's calculation result as I need the calculation to be on-the-fly.

Any tips or workaround idea would be very much appreciated! Thank you!

[v-chenwuz-msft]

Hi @rigiyeh ,

Indeed, it is not possible to compute formulas with circular dependencies in power bi.

However, we can avoid this problem from a mathematical point of view. It seems to follow from the screenshot that x=y, and you can try to prove it.

If i misunderstood you, please share some example data and expcet result.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rigiyeh]

Hi @v-chenwuz-msft 

you were right about the example here (sorry I was trying to simplify the problem to see if I can find a general solution for a recursive function)

The actual problem I was trying to solve is a bit more complicated than the example, where

y1 = IF(x1 >0, x1, 0)

y2 = IF( (x1+x2)-(y1) >0, (x1+x2)- (y1), 0)

and so on

so x is only equal to y when the difference between cumulative x and last cumulative y is all positive 

Any suggestions for this? Thank you!

[v-chenwuz-msft]

Hi @rigiyeh ,

To my knowledge, this doesn't seem very possible. You could try doing the calculations in excel first and then import it into power bi.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jos_Woolley]

Perhaps post some more realistic data (copiable, not a picture), together with expected results.

Regards

[amitchandak]

@rigiyeh , based on screen shot, seem like you need

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

[rigiyeh]

hi @amitchandak ,

thanks for the quick response! I did tried something like that, however, as the current month's [Y] is based on it's previous month's cumulative [Y] result, it will cause a circular dependency error if I tried to calculate cumulative [Y]

Current month Y = 
CALCULATE(SUMX(MyTable, [Cumulative X] - [Cumulative Y (last month)]))

Cumulative Y (last month) = 
var ThisMonth = SELECTEDVALUE(MyCalendar[Month])
var LastMonth = EDATE(ThisMonth, -1)
return CALCULATE(SUMX(MyTable, [Current month Y]), All(MyCalendar), MyCalendar[Month] <= LastMonth)) 

[daXtreme · Accepted solution]

Hi @rigiyeh 

If you have a problem that calls for recursion in DAX... well, then you've basically got 2 choices: 1) either you'll find a non-recursive formula or 2) you'll move the calculation to Power Query/source system. The best solution is to be able to find a non-recursive formula, of course, and sometimes it's possible with a bit of mathematical wizardry but sometimes, sadly, it's simply impossible or just too difficult to do. Then, of course, you don't have a choice 😞 By experience I can tell you that if you have a formula with IF's in it and it's recursive, then there are very slim chances to obtain an iterative one...