YoY Calculation for non cumulative data

[tashaq]

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:

https://www.dropbox.com/s/zi46j3t72qri0pe/Sample%20YoY%20File.pbix?dl=0

Thanks in advance.

[tashaq]

Hi,

Ive simplified and added a sample file pbix below. As mentioned, Id like to be able to include YoY comparisons looking at standalone historical periods and not cumulative totals.

Many thanks in advance.

https://www.dropbox.com/s/zi46j3t72qri0pe/Sample%20YoY%20File.pbix?dl=0 

[Kumail]

Hello @tashaq 

The file is attached in the URL below for your reference.

https://drive.google.com/file/d/1IjgpKKVHXhdI8LQ9yddr5ZIR3svP7VdG/view?usp=sharing

Regards

Kumail Raza

Did this help? Kudos are appreciated

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

[Kumail · Accepted solution]

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

[tashaq]

Thank you @Kumail, thats great.

Just so I understand going forwards, and so i can replicate this for other measures, was the issue simply the reference being used for the 'Year', rather than any issues with the measures or the dateTable itself?

[Kumail]

Hello @tashaq 

If you could send sample .pbix that demonstrate what you are looking to get. It would really help providing you a quick solution.

You can send the sample .pbix file by adding it to your drive or dropbox and add the link here. 

Regards
Kumail Raza

[amitchandak]

@tashaq , Need sample data. But I think My blog on similar topic can help

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...

[tashaq]

Still struggling - can anyone help with this please or have any thoughts?