Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
John_W
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.

 

2024-07-18_17-41-02.png

 

Here is how it would look in Excel.

 

2024-07-18_17-57-00.png

 

Thanks for looking.

 

John

1 ACCEPTED SOLUTION

Hi,

PowerBI file attached.

Hope this helps.

Ashish_Mathur_0-1721654305550.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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
https://www.linkedin.com/in/excelenthusiasts/

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.

Hi,

PowerBI file attached.

Hope this helps.

Ashish_Mathur_0-1721654305550.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
rajendraongole1
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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