Forum Discussion

STIBBS_807's avatar
STIBBS_807
Resolver I
4 years ago
Solved

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 Inta...
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    4 years ago

    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.

STIBBS_807's avatar
STIBBS_807
Resolver I
4 years ago

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?