Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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])
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
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)
Thank you very much guys.
It works!
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])
Thank you very much Stachu.
I tried this code, but I get the error message: Token Eof expected
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
User | Count |
---|---|
94 | |
79 | |
74 | |
63 | |
59 |
User | Count |
---|---|
110 | |
101 | |
84 | |
64 | |
62 |