Show sales only for corresponding months in prior years

{ 2 Comments }

Refer to this simple Sales dataset

untitled

The objective is to create a simple matrix with months in the row labels, years in the column labels and sales figures in the value area section.  The twist in the question is that for years prior to the current year (2018 in this dataset), sales should only appear till the month for which there is data for the current year.  For e.g., for 2018, data is only till Month 4 and therefore for prior years as well, data should only appear till Month 4.  As and when Sales data gets added below row 17, data for prior years should also go up to that month.

The expected result is

untitled1

You may download my PBI file from here. The same solution can be obtained in Excel as well (using Power Query and PowerPivot).

Leave a Comment Cancel reply

Your email address will not be published.

*

  • Will this work if we don’t choose the current year. E.g. if I have data from 2017-20, but don’t want to compare 2020, and only want to compare 2017-19. In the slicer, I will exclude 2020. Will it show the complete year of data for 2017-19 in that case?