Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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: ...
  • daXtreme's avatar
    daXtreme
    4 years ago

    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...

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
daXtreme
Solution Sage
4 years ago

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...