Calculate value based on percentage growth (measure)

[Anonymous]

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!

[PaulOlding · Accepted solution]

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(

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

[Anonymous]

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]

@Anonymous ,

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

[Anonymous]

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