cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KYEW123
Regular Visitor

Calculate value based on percentage growth (measure)

Hi all,

 

I'm trying to calculate values based on growth percentages that are given. The inventory and growthpercentages are both measures. The result would look like this (Forecast column) :

Year

Quarter   

Inventory   

Forecast  

GrowthPercentage

2020

Q1

8766

 

 

2020

Q2

7876

 

 

2020

Q3

9765

 

 

2020

Q4

8003

 

 

2021

Q1

 

8611,23

         108%

2021

Q2

 

9429,29

         110%

2021

Q3

 

10056,34

         107%

2021

Q4

 

10916,16

         109%

 

My thought are to first get the latest value known value: 8003 and then it should multiply for 2021 with 108% (result 8611,23). That result should be multiplied by the percentage of next quarter (8611,23 * 110%) etc.

 

I can't come up with a solution to realise this through a measure. Are there any idea's or examples on how to realize this?

 

If more info is needed please ask.

 

Thanks in advance!

1 ACCEPTED SOLUTION
PaulOlding
Super User
Super User

Here's a measure that follows your idea to get the last known value. 

Then it multiplies that by the compound growth.  That is, the growth percent of this quarter and all previous quarters multiplied together.

It relies on there being  'Quarter Year Order' column in your date table rather than seperate year and quarter columns.  LASTNONBLANK takes just a single column.

 

PaulOlding_0-1620764438118.png

 

Forecast =
VAR _LastInventory =
CALCULATE(
[Inventory],
LASTNONBLANK(ALL('Date'[Quarter Year Order]), [Inventory])
)
VAR _CurrentQuarter = MAX('Date'[Quarter Year Order])
VAR _CompoundGrowth =
PRODUCTX(
ADDCOLUMNS(
CALCULATETABLE(VALUES('Date'[Quarter Year Order]), 'Date'[Quarter Year Order] <= _CurrentQuarter),
"@Growth", [GrowthPercentage]
),
[@Growth]
)
RETURN
_LastInventory * _CompoundGrowth

 

* NB: Are you sure you've calculated the amounts in red correctly? 8003 * 1.08 = 8643.24

View solution in original post

4 REPLIES 4
PaulOlding
Super User
Super User

Here's a measure that follows your idea to get the last known value. 

Then it multiplies that by the compound growth.  That is, the growth percent of this quarter and all previous quarters multiplied together.

It relies on there being  'Quarter Year Order' column in your date table rather than seperate year and quarter columns.  LASTNONBLANK takes just a single column.

 

PaulOlding_0-1620764438118.png

 

Forecast =
VAR _LastInventory =
CALCULATE(
[Inventory],
LASTNONBLANK(ALL('Date'[Quarter Year Order]), [Inventory])
)
VAR _CurrentQuarter = MAX('Date'[Quarter Year Order])
VAR _CompoundGrowth =
PRODUCTX(
ADDCOLUMNS(
CALCULATETABLE(VALUES('Date'[Quarter Year Order]), 'Date'[Quarter Year Order] <= _CurrentQuarter),
"@Growth", [GrowthPercentage]
),
[@Growth]
)
RETURN
_LastInventory * _CompoundGrowth

 

* NB: Are you sure you've calculated the amounts in red correctly? 8003 * 1.08 = 8643.24

View solution in original post

Hi PaulOlding,

 

I've worked around a bit with this example you presented, very much appreaciated btw!

IThe _LastInventory at my side is still not working fully but the PRODUCTX was very helpfull and is working, thank you!

 

 

amitchandak
Super User
Super User

@KYEW123 ,

Create a tbale with Distinct Year and QTr and add column (Say date table)

 

New columns

Year Qtr = [Year] & [Qtr]

Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

 

Measures
This Qtr = CALCULATE(sum('Table'[Forecast]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('Table'[Inventory]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-4))

growth % = divide([This Qtr],[Last Qtr])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@amitchandak 

Thank you for replying.

 

That would calculate the percentages, but the percentages are already given in my situation. I'm trying to calculate the value growth based on these calculations (the red numbers in my post).

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.