How do I show the difference in sales each year compared to a reference year?

[captdunco]

Hi guys,
I am new to power pivot (Less than a week). I am trying to calculate determine the cost difference for a given product each year compared to a give n reference year. My data is as below:

Product	Year	Cost
A	2015	23
A	2016	10
A	2017	6
A	2018	4
A	2019	4
B	2015	35
B	2016	23
B	2017	21
B	2018	20
B	2019	20

<colgroup><col span="3" width="64" style="width: 48pt;"></colgroup><tbody style="border: 0px; font-weight: inherit; font-style: inherit; font-family: inherit; margin: 0px; outline: 0px; padding: 0px;">
</tbody>

Ideally , I want to summarize the above into a table as below showing the difference in cost in each year per product compared to 2015. Below is an illustration.

Difference in Cost per year compared to 2015
Product	2015	2016	2017	2018	2019
A	0	-13	-17	-19	-19
B	0	-12	-14	-15	-15

<colgroup><col span="6" width="64" style="width: 48pt;"></colgroup><tbody style="border: 0px; font-weight: inherit; font-style: inherit; font-family: inherit; margin: 0px; outline: 0px; padding: 0px;">
</tbody>

I have tried several measures but they all seem to give wrong figures. I am out of ideas.
I am a total newbie and am supposing this could be very easy to most of you but I would appreciate help ideas on how to go about this.
Thanks.



 

[Matt Allington]

You will definitely need to build your knowledge. You should create a calendar table.
Power Pivot Calendar Tables - Excelerator BI

then the approach really depends on your data. How many rows in the data table? How many products? What granularity is your data? Is it yearly like shown? I recommend you read my book as it was designed to help you get started Learn to Write DAX - Excelerator BI. In the mean time, post the above info and I will try to help you.

 

[captdunco]

You will definitely need to build your knowledge. You should create a calendar table.
Power Pivot Calendar Tables - Excelerator BI

then the approach really depends on your data. How many rows in the data table? How many products? What granularity is your data? Is it yearly like shown? I recommend you read my book as it was designed to help you get started Learn to Write DAX - Excelerator BI. In the mean time, post the above info and I will try to help you.

Hi Matt,

Thank you very much for the offer. I will definitely check out your book.

I have uploaded the excel file I was working on to dropbox:

https://www.dropbox.com/s/ulqepi127dhh1dc/ExampleBook.xlsx?dl=0

It is just 30 rows. ( I run a small business!)
It is a list of the products and their costs for the years 2015-2020. I have linked a calendar table with all the dates in the period.

As you will see from my attempt at the CostDifferencecomparedto2015 pivot table on the 2nd tab, I can't seem to get the measure to return the difference in cost for each year compared to 2015.

 

[Matt Allington]

If your data is only yearly (like in this example) then you probably don't need a calendar table. The reason your formula is not working is that you have referred to the wrong date column in your formula cost2015. If you are using a calendar table in your pivot table (like you have), then your formulas must use the calendar table. Or delete the calendar table and use the Year column from your data table and it will work.

 

[pociners]

Hi guys,
I am new to power pivot (Less than a week). I am trying to calculate determine the cost difference for a given product each year compared to a give n reference year. My data is as below:

Product	Year	Cost
A	2015	23
A	2016	10
A	2017	6
A	2018	4
A	2019	4
B	2015	35
B	2016	23
B	2017	21
B	2018	20
B	2019	20

<colgroup><col span="3" width="64" style="width: 48pt;"></colgroup><tbody style="border: 0px; font-weight: inherit; font-style: inherit; font-family: inherit; margin: 0px; outline: 0px; padding: 0px;">
</tbody>

Ideally , I want to summarize the above into a table as below showing the difference in cost in each year per product compared to 2015. Below is an illustration.

Difference in Cost per year compared to 2015
Product	2015	2016	2017	2018	2019
A	0	-13	-17	-19	-19
B	0	-12	-14	-15	-15

<colgroup><col span="6" width="64" style="width: 48pt;"></colgroup><tbody style="border: 0px; font-weight: inherit; font-style: inherit; font-family: inherit; margin: 0px; outline: 0px; padding: 0px;">
</tbody>

I have tried several measures but they all seem to give wrong figures. I am out of ideas.
I am a total newbie and am supposing this could be very easy to most of you but I would appreciate help ideas on how to go about this.
Thanks.



Hello.. You can check in this video for your case
https://www.youtube.com/watch?v=kMMohkVk8Ds
Well this video is not mine actually, but you can learn from it.