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

C

captdunco

New Member
Joined
Mar 15, 2016
Messages
2
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:


[TABLE="width: 192"]
<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;">[TR]
[TD="width: 64"]Product[/TD]
[TD="width: 64"]Year[/TD]
[TD="width: 64"]Cost[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]20[/TD]
[/TR]
</tbody>[/TABLE]
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.
[TABLE="width: 384"]
<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;">[TR]
[TD="width: 320, colspan: 5"]Difference in Cost per year compared to 2015[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]Product[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-13[/TD]
[TD="align: right"]-17[/TD]
[TD="align: right"]-19[/TD]
[TD="align: right"]-19[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-12[/TD]
[TD="align: right"]-14[/TD]
[TD="align: right"]-15[/TD]
[TD="align: right"]-15[/TD]
[/TR]
</tbody>[/TABLE]
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.


 
Sort by date Sort by votes
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.
 
Upvote 0
Matt Allington said:
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.
Click to expand...

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.
 
Upvote 0
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.
 
Upvote 0
captdunco said:
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:


[TABLE="width: 192"]
<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;">[TR]
[TD="width: 64"]Product[/TD]
[TD="width: 64"]Year[/TD]
[TD="width: 64"]Cost[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2019[/TD]
[TD="align: right"]20[/TD]
[/TR]
</tbody>[/TABLE]
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.
[TABLE="width: 384"]
<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;">[TR]
[TD="width: 320, colspan: 5"]Difference in Cost per year compared to 2015[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]Product[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2017[/TD]
[TD="align: right"]2018[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-13[/TD]
[TD="align: right"]-17[/TD]
[TD="align: right"]-19[/TD]
[TD="align: right"]-19[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-12[/TD]
[TD="align: right"]-14[/TD]
[TD="align: right"]-15[/TD]
[TD="align: right"]-15[/TD]
[/TR]
</tbody>[/TABLE]
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.


Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
K
  • Question Question
Dynamic DAX calculation between two rows of same DAX measure (filtered rows)
Replies
3
Views
3K
kalam1989
K
K
  • Question Question
How to remove duplicates from individual powerquery columns without removing entire rows
Replies
3
Views
1K
andydtaylor
A
P
  • Question Question
Formula that checks 4 different parameters before giving a solution
Replies
4
Views
496
Legacy 143009
L
L
  • Question Question
Relating data from two different periods
Replies
0
Views
662
Lamati
L

Forum statistics

Threads
1,226,840
Messages
6,193,274
Members
453,788
Latest member
drcharle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top