Dynamic Month X axis with Y axis showing count of values that are fall into that month

[Beth_H]

Hi there.

I have some data that looks similar to the example below. I have a selection of Placement Ids and if they are active in a particular month, then they are flagged with a 1.

I want to create a bar chart that shows the name of month (3 months ago, 2 months ago and then last month) on the x axis, and then the total count of Placements on the y axis (i.e. placements flagged in more than one month will be counted multiple times on the same bar chart).

Placement ID	3 Months Ago	2 Months Ago	Last Month
1	1	1
2	1	1
3	1	1
4		1	1
5		1	1
6	1	1	1
7	1		1
8	1		1
9	1		1
10		1	1
11			1
12		1	1
13	1	1
14	1	1
15		1	1
16		1	1
17		1	1
18		1	1
19	1	1	1
20	1	1	1

So far I have managed to create this:

By creating a dynamic Month Table for the x axis shown below:

And a measure for the y axis written as follows:

     PopulationByMonth = SWITCH(MAX('Month Table'[Month]),

     FORMAT(DATE(YEAR(EDATE(TODAY(), -3)), MONTH(EDATE(TODAY(), -3)), 1), "MMMM"), COUNT(Table[Month1]),

     FORMAT(DATE(YEAR(EDATE(TODAY(), -2)), MONTH(EDATE(TODAY(), -2)), 1), "MMMM"), COUNT(Table[Month2]),

     FORMAT(DATE(YEAR(EDATE(TODAY(), -1)), MONTH(EDATE(TODAY(), -1)), 1), "MMMM"), COUNT(Table[Month3])

     )

The numbers are correct! But for the life of me I cannot sort the x axis so that it shows the Months in order. Even though I have added an Index to the Month Table and set that table to Sort by Column > Index, I cannot change it.

Is there a way to fix this?

Or is there another way to achieve the bar chart that I want?

[Anonymous]

Hi,@Beth_H 

I am glad to help you.   

According to your description, you want to sort the x-axis so that it displays the months in order？ 

If I understand you correctly, then you can refer to my solution.  

First, open the Power Query Editor and find Column From Examples. The exact location is as follows: 

Then you can set the order in which you want to display them: 

Then click the column you want to sort and modify the sorting conditions: 

This is the final result: 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Beth_H · Accepted solution]

Hi Fen Ling, 

Thank you for your response, however in my post I described how I had already done these steps. My Month table had an index which was sorted by Index but this made no difference to the order.

But I found a fix myself!

In the end I added the Index to the visual itself as a tooltip. This added the Index as an option in the 'Sort axis' option (shown below) and I now have my axis ordered according to the index.

Thanks.