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 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's avatar
v-chenwuz-msft
Community Support
4 years ago

Hi Anonymous ,

 

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.