Forum Discussion
Recursion in DAX
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!
Hi Anonymous
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...
7 Replies
- amitchandakSuper User
Anonymous , 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
- AnonymousNot applicable
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))
- daXtremeSolution Sage
Hi Anonymous
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...
- v-chenwuz-msftCommunity Support
Hi Anonymous ,
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.
- AnonymousNot applicable
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!
- Jos_WoolleySolution Sage
Perhaps post some more realistic data (copiable, not a picture), together with expected results.
Regards