Dim slicer - replace Month with Year (need help)

[Anonymous]

Hi all

I want to be able to change my chart dim from month to a year (be able to view the chart by years or by months).

I have created a dim table for the slicer: 

AXIS SLICER =

UNION(

CROSSJOIN(ROW("Axis", "Year"), VALUES('Date'[Year])),

CROSSJOIN(ROW("Axis", "Month"), VALUES('Date'[Month]))

)

This table contains now:

Axis         Year

Year	2010
Year	2011
Year	2012
Year	2013
Year	2014
Year	2015
Year	2016
Year	2017
Year	2018
Year	2019
Month	Jan
Month	Feb
Month	March
Month	April

And so on till December..

Now, my chart has 'Year' column in the axis, and my slicer has 'Month' and 'Year' buttons while in the category field it's 'Axis' column from the table I have created. 

My problem is that this solution not calculating right the values in the chart. My values in the chart are some type of average, the same value for each Year/Month. 

Am I missing something?

Thank you in advance. 

[MFelix]

Hi @Anonymous ,

Not sure how you have your data setup but since you are making this dimension table to change the axis you need to make a measure for each of your calculations.

When you have two tables that are not related and you use one of them on your visuals to give context (in this case the axis) and the values are from another table you will get the full sum (average, count,...) on the table of the values since there is no relation between axis and values so it pick up everything on the value side.

Based on this create a measure for the calculation of the YEARS / MONTHS and using a switch measure return the value to your chart.

Check this post with an explanation how to achieve this.

I have also authored a post in the community with 3 options to get this made, please also see different possibilities to achieve this.

Regards,

MFelix