Hi Marko,

If you have proper dates in your model, which you should, then you can use the time intelligence functions like SAMEPERIODLASTYEAR etc. to find the price for the previous year and calculate the difference.

Mynda

Hi Mynda,

thank you for quick reply.

I have tried to implement function that you suggested, but with no result. I am not much familiar with DAX syntax.

Kindly ask you to check attached file with sample of data.

Thank you for support.

BR,

Marko

Hi Marko,

Thanks for sharing your file. There is an easier way. Simply right-click an of the values in the PivotTable > Show Values As > Difference From > Year and Previous.

Mynda

Hi Mynda,

thanks a lot.

When I want to create bar chart, is it possible to hide the columns for 2021? I would like to show only difference.

Best regards,

Marko

Hi Marko,

No, the PivotTable will always display the prior period and the chart will always display the data from the PivotTable. If you add a Slicer for the Brand you can then remove the legend and the chart will only display the brand selected in the Slicer. You can also set the series overlap to 100% and this will centre the bar to the InchSize label.

Hope that helps.

Mynda

Hi Mynda,

thanks for hints. It works great.

But I found out some other solution to work around the pivot table. I copied PT and past it as value, after that I created bar chart from this new table and edit source data to reference to my PT. I am not sure will it work in the future when my master table expand with new data.

I encountered on other difficulties now. How can I compare prices between two different brands for the same year?

Best regards,

Marko

If you've pasted the data as values then the chart won't reflect any updates to the PivotTable, so not ideal as you'd have to copy and paste as values each time you update the data.

You can also compare the brands using the Show Values As > Difference From setting.

Mynda

Hi Mynda,

You are 100% right. I have tested in the meanwhile and chart does not reflect any updates to the PivotTable.

So I had to drop out this solution, because it is not efficient.

I have to dive in more in DAX formulas and syntax. It seems to me to be much more powerful than the regular excel formulas.

What do you think is it better to have multiple tables (each for one brand) connected in one table or to have one big table with all data in it (that is my case at this moment)?

I am thinking to create tables for each brand separately and after that to connect them through the data model. Maybe is easier to calculate some measures in this way.

Best regards,

Marko

Hi Marko,

Definitely better to have one big table with all your data, than split it into separate tables for each brand. Having separate tables for each brand is not the way Excel or Power Pivot was designed to be used, so you will only come up against issues if you do that.

As I mentioned before, you can use Show Values As to calculate the difference between two different brands for the same year. See attached.

Mynda

Hi Mynda,

thank you so much for support and effort.

Best regards,

Marko