cancel
Showing results for
Did you mean:
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?

1 ACCEPTED SOLUTION
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.

Forecast =
VAR _LastInventory =
CALCULATE(
[Inventory],
LASTNONBLANK(ALL('Date'[Quarter Year Order]), [Inventory])
)
VAR _CurrentQuarter = MAX('Date'[Quarter Year Order])
VAR _CompoundGrowth =
PRODUCTX(
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

4 REPLIES 4
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.

Forecast =
VAR _LastInventory =
CALCULATE(
[Inventory],
LASTNONBLANK(ALL('Date'[Quarter Year Order]), [Inventory])
)
VAR _CurrentQuarter = MAX('Date'[Quarter Year Order])
VAR _CompoundGrowth =
PRODUCTX(
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

Regular Visitor

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!

Super User

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])

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

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).

Announcements

#### Launching new user group features

Learn how to create your own user groups today!