Forum Discussion
Simple Growth Measure
I have the following data that I would like to do a growth % for:
Year Totals Case Type Year End
| 2017 | 1420 | Intake | 31/12/2017 |
| 2018 | 1520 | Intake | 31/12/2018 |
| 2019 | 1358 | Intake | 31/12/2019 |
| 2020 | 1079 | Intake | 31/12/2020 |
| 2021 | 802 | Intake | 31/12/2021 |
| 2017 | 557 | Investigation | 31/12/2017 |
| 2018 | 473 | Investigation | 31/12/2018 |
| 2019 | 421 | Investigation | 31/12/2019 |
| 2020 | 410 | Investigation | 31/12/2020 |
| 2021 | 336 | Investigation | 31/12/2021 |
| 2017 | 233 | Ongoing | 31/12/2017 |
| 2018 | 186 | Ongoing | 31/12/2018 |
| 2019 | 154 | Ongoing | 31/12/2019 |
| 2020 | 145 | Ongoing | 31/12/2020 |
| 2021 | 122 | Ongoing | 31/12/2021 |
Measure 1 is:
Prev Year= CALCULATE(SUM('TABLE NAME'[Totals]),SAMEPERIODLASTYEAR('TABLE NAME'[Year End]))
Measure 2 is:
%Growth =CALCULATE(DIVIDE(SUM('TABLE NAME'[Totals])- [Prev Year],[Prev Year])*1)
I am not getting my Prev Year value in the column. I get an over all total of 1151 and zero growth.😫
Here is the results that I would like to get:😀
| Year | Totals | Prev Year | %Growth |
| 2017 | 2210 | ||
| 2018 | 2179 | 2210 | -1% |
| 2019 | 1933 | 2179 | -11% |
| 2020 | 1634 | 1933 | -15% |
| 2021 | 1260 | 1634 | -23% |
Please review my measures and tell me what I am missing. Thank you.
Alternatively I would like to see this:
| Case Type | Year | Totals | Prev Year | Growth% |
| Intake | 2017 | 1420 | ||
| Intake | 2018 | 1520 | 1420 | 7% |
| Intake | 2019 | 1358 | 1520 | -11% |
| Intake | 2020 | 1079 | 1358 | -21% |
| Intake | 2021 | 802 | 1079 | -26% |
| Investigation | 2017 | 557 | ||
| Investigation | 2018 | 473 | 557 | -15% |
| Investigation | 2019 | 421 | 473 | -11% |
| Investigation | 2020 | 410 | 421 | -3% |
| Investigation | 2021 | 336 | 410 | -18% |
| Ongoing | 2017 | 233 | ||
| Ongoing | 2018 | 186 | 233 | -20% |
| Ongoing | 2019 | 154 | 186 | -17% |
| Ongoing | 2020 | 145 | 154 | -6% |
| Ongoing | 2021 | 122 | 145 | -16% |
I have Filters on my page so I can use that to get the Case Types if this is not achievable.
STIBBS_807 Do you have a DimDate table? https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Time Intelligence is MUCH easier with a date table.
Follow the instructions in my blog if you don't have one, then try these measures:
Total Value = SUM('TABLE NAME'[Totals])
PY Total Value = CALCULATE ( [Total Value] , DATEADD( DimDate[Date], -1, YEAR) )
% Growth Totals = DIVIDE( [Total Value] - [PY Total Value], [PY Total Value] )
Make sure you use that % Growth within the context of a Year or date slicer/visual.
5 Replies
STIBBS_807 Do you have a DimDate table? https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Time Intelligence is MUCH easier with a date table.
Follow the instructions in my blog if you don't have one, then try these measures:
Total Value = SUM('TABLE NAME'[Totals])
PY Total Value = CALCULATE ( [Total Value] , DATEADD( DimDate[Date], -1, YEAR) )
% Growth Totals = DIVIDE( [Total Value] - [PY Total Value], [PY Total Value] )
Make sure you use that % Growth within the context of a Year or date slicer/visual.
Thank you. I do have a DimDate table set up. I will try your solution and let you know if it works for me.
Almost there. Now I have the totals but the Previous Year do not line up correctly.
Total IIO = SUM('Table Name'[Totals])PY IIO = CALCULATE([Total IIO],DATEADD('Calendar'[Date],-1,YEAR))IIO %Growth = CALCULATE( divide(([Total IIO] -[PY IIO]),[PY IIO]))My Calendar table is set up like so:
So I am not sure What I have done wrong in the Matrix. Do I have my Calendar Table set up incorrectly?
Found my Problem. The calendar table that I imported was not defined as a Date Table. Once I did that all when well. Thank you for your assistance and patience.
STIBBS_807 Glad you got it working - that mark as date table is a hidden trick that catches a lot of people. I struggled with it for ages when I started learning Power BI and couldn't figure out why my calculations weren't working!
