Recursive Measure Calculation Problem in Power BI

[bonus1]

Hey guys,

I am struggling with the following problem.

I want to create a measure which calculates recursively the Value for ne current Month.

I just want to use the recursion formular:

X(Month) = X(Month-1) + Y(Month) - Z(Month)

For example for this table:

Means, the measure should start by February.

X(February) = X(January) + Y(February) - Z(February)

X(February) = 295 + 374 - 454

X(February) = 215

For March:

X(March) = X(February) + Y(March) - Z(March)

X(February) = 215 + 580 - 782

etc.

Can anyone help me please to solve this problem within Power BI?

Thank you very much in advance.

[Stachu · Accepted solution]

to my knowledge there really is no recursive functionality in DAX
still you can achieve the similar result with iterator
try this code (calculated column)

Column = 
VAR CurrentMonth = 'Table'[Date]
VAR InitialValueDate = MIN('Table'[Date])
VAR TableYZ = FILTER('Table', 'Table'[Date]<=CurrentMonth && 'Table'[Date] > InitialValueDate)
RETURN
CALCULATE(SUM('Table'[X]),FILTER(ALL('Table'),'Table'[Date]<CurrentMonth))
+ SUMX(TableYZ,[Y]-[Z])

[bonus1]

Hey @v-juanli-msft, @Stachu

I have a nother problem in this relation.

If the X column has in each row a value, the formular adds this value as well in X(Month-1). But that should not be the case, it should just takes the new calculated value in X(Month-1)

[bonus1]

Does anyone have an idea?

[bonus1]

Thank you very much guys.

It works!

[Stachu · Accepted solution]

to my knowledge there really is no recursive functionality in DAX
still you can achieve the similar result with iterator
try this code (calculated column)

Column = 
VAR CurrentMonth = 'Table'[Date]
VAR InitialValueDate = MIN('Table'[Date])
VAR TableYZ = FILTER('Table', 'Table'[Date]<=CurrentMonth && 'Table'[Date] > InitialValueDate)
RETURN
CALCULATE(SUM('Table'[X]),FILTER(ALL('Table'),'Table'[Date]<CurrentMonth))
+ SUMX(TableYZ,[Y]-[Z])

[bonus1]

Thank you very much Stachu.

I tried this code, but I get the error message: Token Eof expected

[v-juanli-msft · Accepted solution]

Hi @bonus1

Stachu's formula helps a lot.

You need to create a calculated column in Modeling->calculated column in Data Model view

Then write the formula to get [Column], then get [Column2] based on [Column], finally, get the measure as you like

My test 

Column = 
VAR CurrentMonth = Sheet1[date]
VAR InitialValueDate = MIN(Sheet1[date])
VAR TableYZ = FILTER(Sheet1,Sheet1[date]<=CurrentMonth && Sheet1[date] > InitialValueDate)
RETURN
CALCULATE(SUM(Sheet1[x]),FILTER(ALL(Sheet1),Sheet1[date]<CurrentMonth))
+ SUMX(TableYZ,[Y]-[Z])

Column 2 = IF([x]<>BLANK(),[x],[Column])

Measure = MAX([Column 2])

Best Regards

Maggie