Simple Growth Measure

[STIBBS_807]

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]

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.

[AllisonKennedy]

@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!

[STIBBS_807]

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?

[STIBBS_807]

Thank you.  I do have a DimDate table set up.   I will try your solution and let you know if it works for me.

[AllisonKennedy · Accepted solution]

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