Forum Discussion
Creative_tree88
Helper V
1 year agoProjected Growth based on previous two years & Displayed on Chart
Hi all - I have a dataset which looks at the last 3 years of customer numbers. What I need to do is project these numbers and show up to the financial year 2029-30 (01 April 2029 to 31 March 2030). ...
speedramps
Super User
1 year agoTry this ...
Input data =
Output report =
This DAX calculates the Brought Forward figure for each year
Brought Fwd =
// get year cursor
var myyear = SELECTEDVALUE('Yourdata'[Year])
// create a temp table for old years
var oldyearstable =
FILTER(
ALLSELECTED('Yourdata'),
[Year] < myyear
)
// insert a column to the temp table with the CAGR (compound annual growth rate)
var growthtable =
ADDCOLUMNS(
oldyearstable ,
"cagr",
// for each row in the oldyears table get the old year cursor
var oldyear=[Year]
// create a temp table off all the oldyears that >= that the old year cursor
var cagryearstable=FILTER(oldyearstable , [Year]>=oldyear)
RETURN
// compute the CAGR (compound annual growth rate) for the temp year. For example (1+Y1%) + (1+Y2%) + (1+Y3%)
PRODUCTX(
cagryearstable,
1+[Interest rate%]
)
)
RETURN
// iterate the sum of the cash growth using the CAGR column
SUMX(
growthtable ,
[Cash injection] * [cagr])
Check the maths in Excel
Please click thumbs up because I put a lot of effort in here for you
then click [accept solution] if it works