The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
94 | |
80 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |