Measure To Calculate Previous Year / Same Month in clustered columns

[pe2950]

I'm trying to create a clustered column chart that would show a count of leads, by month, with each cluster representing the same month. 

I've got the data in the format i want it, the query has two columns one that is start of month date, and next is a sum column that is a count of the amount of leads. 

I can't quite get how to group the clusters to show the same months for previous years, such that cluster 1 would contain january 2017, january 2016, january, 2015etc 

Any advice?

[v-sihou-msft · Accepted solution]

@pe2950

In this scenario, if you want to put same month in all years into one cluster, you can add Year column and Month Number column in your dataset, then you just need to put Month Number on X-axis, put Year on Legent and populate Count Of Leads into Value.

Regards,

[tsapat]

You can create a custom column in your Calendar table (assuming you have already created one) that displays the Month / Year of the calendar date, for example: 

Month/Year = FORMAT(Calendar[Date];"mmm YYYY")

or in any desired format that you want.

Then in the clustered chart, set the Month / Year as an axis element and the corresponding measure that calculates the sum of leads as a value element.