Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm fairly new to PowerBI and am trying to figure out how to do this. I've done quite a bit of googling and couldn't find a solution this is issue. I have a calculated table in PowerBI similar to columns F/G (Or a measure that outputs that percent as well if it is easier). I would like to calculate columns J to be able to graph, to tell a better overall picture than graphing G. This is easy in excel, make the first column 100, and then for every next row do, (the last row) * (1+ Weighted % Change). However after about 4-5 hours of trying and researching I couldn't figure out how to get this to work in PowerBI.
Solved! Go to Solution.
Hi @askelton,
You can refer to below formulas to calculate current row with recursive previous row:
Create a new table with original data and new row(first date):
Merged = var temp= FIRSTDATE(Table1[Month]) return UNION(ROW("Month",DATE(YEAR(temp),MONTH(temp)-1,DAY(temp)),"Weight % Change",0),Table1)
Measures:
Default Amount = if(MAX([Month])=FIRSTDATE(ALL(Merged[Month])),100) Multiply Percent = IF ( ISBLANK ( [Default Amount] ), 1 + MAX(Merged[Weight % Change]), [Default Amount] ) Cumulated LN = CALCULATE ( SUMX ( FILTER(ALL(Merged),[Month]<=MAX(Merged[Month])), LN ( [Multiply Percent] ) ) ) Recursive Calculations = SUMX ( VALUES ( 'Merged' ), IF ( ISBLANK ( [Default Amount] ), EXP ( [Cumulated LN] ), [Default Amount] ) )
Reference link:
Recursive Calculations in PowerPivot using DAX
Regards,
Xiaoxin Sheng
Hi @askelton,
You can refer to below formulas to calculate current row with recursive previous row:
Create a new table with original data and new row(first date):
Merged = var temp= FIRSTDATE(Table1[Month]) return UNION(ROW("Month",DATE(YEAR(temp),MONTH(temp)-1,DAY(temp)),"Weight % Change",0),Table1)
Measures:
Default Amount = if(MAX([Month])=FIRSTDATE(ALL(Merged[Month])),100) Multiply Percent = IF ( ISBLANK ( [Default Amount] ), 1 + MAX(Merged[Weight % Change]), [Default Amount] ) Cumulated LN = CALCULATE ( SUMX ( FILTER(ALL(Merged),[Month]<=MAX(Merged[Month])), LN ( [Multiply Percent] ) ) ) Recursive Calculations = SUMX ( VALUES ( 'Merged' ), IF ( ISBLANK ( [Default Amount] ), EXP ( [Cumulated LN] ), [Default Amount] ) )
Reference link:
Recursive Calculations in PowerPivot using DAX
Regards,
Xiaoxin Sheng
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |