Line and clustered column chart with three different years

[zervino]

I have volume data for different years (and months/periods will represented by columns).

I have 3 slicers and I would like to create a "line and clustered column chart" where the first 2 slicers indicate the years that will appear in columns and the third slicer will be the line.

My raw data has this format:

And the output I want is like below, but having also columns for one more year plus the line.

[Data-estDog]

First off, this is a horrible idea. The proper way to do this is to have the measure you want, Sum of volume, target volume, etc. Add them to the line & Clustered column chart. Add 1 filter that is year. Choose the year Display the actuals with the target. 

If you won't or can't put your foot down..... 

1. Create a measure for each year (example:  Amount2022 = calculate(sum(amount), year = 2022), etc. )
   Create a field parameter.

2. Add all the measures to the field parameter.

(note: using random measures already created for demo purposes)

3. repeat step 1-2 two more times so you have 3 field params. 

4. Add the field params to the chart. (And add the slicers to the page if it wasn't done automatically)

5. Bask in the glory of a rigid and hard to maintian report page that is sure to bring you hours of depression as you scale it manually year after year, measure after measure.

I hope you can appreciate a little humor, I know we often have our hands tied in situations like this. But if the consumers give you any leeway to implement it a different way... take it. 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Data-estDog]

Lots of questions: First, most importantly... what are you try to communicate the end user here?
My guess is you want a way to show previous two years volume by period compared to the current year.

Will people decide which year will be the line for any purpose? Or is the line always the current year and you want previous two years to be the columns?

[zervino]

The user will choose any combination. But most probably will be that the columns will be past years (ie. 2020 vs 2022) and the line some forecast scenario. But ideally they should be able to play with the 3 slicers to decide what year appears in bars or line.