Calculate Growth Based on Previous Row Calculation

[John_W]

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

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[John_W]

Link to sample PBIX 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.

[Ashish_Mathur]

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.

[John_W]

Excel File 

Base Week Ending slicer just allows the user to pick particular week ending dates to restrict the Base data that is used.  The [Base] measure calculates the average based on the Week Endings and the day of week from the Current Date that was selected.  For example, if the user selects Current Date of 7/18 (Thu) and Base Week Endings of 7/13 and 7/6, the Base measure will calculate the average of 7/4 and 7/11 - both Thursdays.  In the full version there would be a lot more weeks they could select from.  

I have uploaded the Excel file which has the formulas and quick descriptions. 

Appreciate you and your time!!!  Thanks.

[Ashish_Mathur · Accepted solution]

Hi,

PowerBI file attached.

Hope this helps.

[John_W]

Amazing...thanks so much.  Here I was trying to do some crazy DAX functions and you approached the problem from a completely different perspective.  Thanks again for your time!

[Ashish_Mathur]

You are welcome.

[rajendraongole1]

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!!

[John_W]

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.