Projected Growth based on previous two years & Displayed on Chart

[Creative_tree88]

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).  We have used a method which is based on calculating growth from the previous two years and then finding the mid point of that growth, to then use as the % for projecting forward.  So, for example if 2010-2011 to 2011-2012 the growth is 10% and then 2011-2012 to 2012-13 growth is 20%, we would use the midpoint of 15% as the % to project to the next year.  This would need to then be projected year on year using the same methodology up to 2029-2030.

I don't know how to do this.  It needs to be shown on a chart, graphically, as well as in table format.

I've attached a sample pbix to try to show you what I mean and for you to use in an effort to help me understand how this can be achieved.  Many thanks!

Sample Data  

[Creative_tree88]

@Ashish_Excel Sorry to bother you.  I am missing some years of projected growth, using the measures exactly as you sent them.  Is it because my years are different to the sample data?  I've gone through all the DAX and they are exactly as you detailed.  My data goes up to FY24/25 - that's the last complete year, but for some reason the projected data misses FY25/26 and FY26/27 and skips to FY27/28...!  I've attached an image showing you my actual numbers I'm returning.  I'm really scratching my head - can you help?  Otherwise it looks fantastic.

[v-priyankata]

Hi @Creative_tree88 

Thank you for reaching out to the Microsoft Fabric Forum Community.

Some projection years like FY25/26 and FY26/27 aren’t showing up because the DAX measure needs customer counts from the past three years to work out the midpoint growth. If even one of those earlier years is missing or blank, the measure can’t calculate the projection and just skips that year.

Thanks.

[v-priyankata]

Hi @Creative_tree88 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[v-priyankata]

Hi @Creative_tree88 

Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted  If yes, marking the releted solution would be awesome for others who might run into the same thing.

[Creative_tree88]

@v-priyankata Thanks for the follow up.  I'm still trying to figure this one out, wass hoping @Ashish_Excel might be able to advise a little.  Thanks.

[v-priyankata]

Hi @Creative_tree88 

We apologize for the inconvenience you are experiencing.
To assist you effectively, could you please share latest pbix file? We kindly request that the data be provided in a usable format (rather than as a screenshot) and that it does not contain any sensitive information or unrelated content.

Thank you for your cooperation!

[v-priyankata]

Hi @Creative_tree88 
We apologize for the inconvenience you are experiencing. We haven’t heard back from you, so I wanted to check if the issue got sorted  if not please share latest pbix file?

[Creative_tree88]

@GSPGSP 
@burakkaragoz 
@Ashish_Excel 

Thanks for all your suggestions.  I've not had a chance to get to this yet due to work pressures, but I'm starting work on this today and rest of this week.  All solutions look really great.  I'll try them all - this is such an amazing forum.  Kudos to you all!

[v-priyankata]

Hi @Creative_tree88 
Thank you for using Microsoft Community Forum.

Have you had a chance to review the solutions provided by @speedramps @burakkaragoz @Ashish_Excel,  if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Creative_tree88]

@v-priyankata I'm going through them now.  It's taking a little time to apply each to my data model, but I'm slowly making my way through.  I've just asked @Ashish_Excel to help with converting from Year to Financial Year, to tie up with my data.  

[v-priyankata]

@Creative_tree88 Sure, thanks for letting me know.

[Ashish_Excel]

Hi,

Download PBI file from here.

Hope this helps.

[Creative_tree88]

@Ashish_Excel I also just need to start projecting from the most recent completed financial year, which in this case is FY24/25 (1st April 2024 to 31st March 2025).  I am really hoping you can help me solve this last piece of the jigsaw as it's very nearly there.

[Creative_tree88]

@Ashish_Excel I've tried to implement and apply your measures to my complete dataset.  The only problem I'm having with it, is that I need to look at this on a financial year basis (our FY runs from 1st April to 31st March every year).  At the moment, your solution is pointing to the 'Year' in the Calendar table which then makes the numbers look different to what I'm used to seeing on a Financial Year basis.

Is there any way of tweaking your measures, or a way of adding Financial Year to the Calendar Table, to tie up with my data?

As always - many thanks and always maximum kudos for the solution you provided.  I look forward to hearing back from you soon.

[Ashish_Excel]

Download the revised PBI file from here.

Hope this helps.

[Creative_tree88]

@Ashish_Excel 

I'm having issues with the projection - it's not tying up with the last date of my actual data, to then project FROM that period (last complete financial year).  See image:



This is further compounded when I select a sub category, related and linked to the main data (see image below):



I'm guessing some sort of tweak of the DAX but I'm not sure how, or what I need to tweak to rectify.

Ignore the 'count ALL Demand' as this is how I've labelled that particular measure, rather than count customers.  It's the same measure though.

Thanks @Ashish_Excel 

[Creative_tree88]

@Ashish_Excel I think part of the issue is that my data goes beyond the last COMPLETE financial year i.e. I already have months April, May for FY25/26.  I need to somehow exclude the current financial year from the DAX calculations I guess.  Are you able to help please @Ashish_Excel ?

[Creative_tree88]

@Ashish_Excel Another one to ponder...I'd like to introduce some sort of 'what if' analysis.  So, 'what if' we lose a venue in a certain year and our customer count has to reduce by, say, 50 per year, how can I introduce this sort of analysis into the projection work you've already produced??  Trying to get my head round how we can gauge the effect on projected growth if we gain, lose venues etc and therefore gain / lose customers - what effect this has on overall projected customer growth over the remaining projected years.  

Any insights using your methodology would be much appreciated.   Thanks @Ashish_Excel 

[Creative_tree88]

@Ashish_Excel Thank you for this, really useful.  I'm not sure how I would adjust the DAX to show projection from the last full financial year (2024-2025).  I have data from financial year 2014-2015 but the last completed financial year is 2024-2025 so how would I adjust the formula to accommodate this?  

Obviously, in your example the projected customer count is from 2013-2014 onwards, having had 3 years of completed data up to that point.  

Just trying to clarify the DAX change to ensure my data is projected from 2024-2025.

Thanks so much.

[Creative_tree88]

@Ashish_Excel if it helps, I've got a field in my data called 'AD_FY' which is already pre calculated to return the financial year.  Not sure whether it would be better to include this in your measures / calculations or refer to the Calendar table to resolve this somehow?