Forum Discussion

tashaq's avatar
tashaq
Regular Visitor
5 years ago
Solved

YoY Calculation for non cumulative data

Hi,

 

First time poster so apologies if this is in the wrong place.

 

I have annual data which is defined at source as academic years (15/16, 16/17 etc) and Ive added a lookup to convert these to dates i.e 31 December year ends. I've created a simple date table and would like to be able to look at the movements (actual/%) between years for individual institutions. 

 

Ive added the measures below, which do work, but the Total provider income is always the cumulative total for all years. If i set a filter for one of the years (i.e 2020), i get the correct Total provider income, but then the PY data/diff is blank as I assume it is only looking at data under the filter set. 

 

I'm likely missing something quite straightforward here so was hoping to get some advice? I just want to be able to compare/look at trends between individual years (periods) and not cumulatively.

 

Measures:

 

Total Provider Income = sum('Merged P&L fee reduction'[Total income])
Total Provider Income PY = CALCULATE([Total Provider Income], DATEADD(DateTable[FullDateAlternateKey],-1,YEAR))
Total Provider Income DIff PY = [Total Provider Income]-[Total Provider Income PY]
Total Provider Income Diff PY % = DIVIDE([Total Provider Income],[Total Provider Income PY],BLANK())-1
 
SAmple Pbix file:
 
 
Thanks in advance.
Need Help
  • Kumail's avatar
    Kumail
    5 years ago

    tashaq 

     

    -You needed to create the relationship in the model between Year Lookup table and DateTable.

     

    Regards

    Kumail Raza

    Did this help? Kudos are appreciated.

    Consider Accept it as the solution to help the other members find it more quickly.

7 Replies