Forum Discussion
Calculate Growth Based on Previous Row Calculation
Hey All! Fairly new to Power BI so please forgive any ignorance.
I have the visual below. I would like to calculate a forecast column based on the Total column and the percent Growth. Expecation is that if a value exists for Total, then simply use Total. If not, then take the Growth percent and add it to the prior row's Total value. The part that is messing me up is that to calculate the further rows, I need to use the "forecast" value from the prior row so that I can apply the Growth rate to it.
So for Hour_Part 11 row, take 2,734,027 (Hour_Part 10's Total) and add 3.3% growth to it. But for Hour_Part 12 row, take the newly calculated 2,824,250 and add 2.46% growth to it...and so on for each row. This is illustrated in the Excel screenshot at the bottom.
Prior Weeks Average, Prior Rwo Average and Growth are all Measures.
Here is how it would look in Excel.
Thanks for looking.
John
Hi,
PowerBI file attached.
Hope this helps.
9 Replies
Hi John_W - create the forecast calculated column as below:
replace with your table name
Calculated column:
Forecast =
VAR CurrentHourPart = TableName[Hour_Part]
VAR CurrentTotal = TableName[Total]
VAR CurrentGrowth = TableName[Growth]
VAR PreviousHourPart = MAXX(FILTER(TableName, TableName[Hour_Part] = CurrentHourPart - 1), TableName[Forecast])
RETURN
IF(
NOT(ISBLANK(CurrentTotal)),
CurrentTotal,
PreviousHourPart * (1 + CurrentGrowth / 100)
)Ensure that your Hour part column has no gaps
Hope it works, still issue exist share the data in drive, will check it.
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!Thanks for looking at it. Unfortunately, this doesn't work.
Total and Growth are measures so creating a calculated column is not going to work for me. (Unless I'm doing something wrong was a probably the case).
I'll work on getting a sample pbix file uploaded.
Hi,
Share the download link of the PBI file.
Looking for a measure that will calculate (1 + Growth) * previous row Acutal. But then subsequent calculations need to be based upon the same calculation from the previous row.
Thanks for looking.
You are welcome. Struggling to understand the data in that file. What is the purpose of the Base Week Endings slicer? Can you also share the Excel file with your formulas intact so that i can fortify my understnding. Not sure of how much i can help but i will try.
