cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

1 ACCEPTED SOLUTION
Super User

Hi,

PowerBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
9 REPLIES 9
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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.

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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.

Super User

Hi,

PowerBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi @John_W - create the forecast calculated column as below:

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!

Proud to be a Super User!

Frequent Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors