Forum Discussion
Helper IIIYear Over Year by Month
Hello Masters!
This should be simple. I have two measures [Forklift New Sales 2016] which sums the 2016 FY GP and [Forklift New Sales 2017] which sums the 2017 FY GP.
I have a Date Table:
Dates = CALENDARAUTO()
Month = FORMAT('Dates'[Date],"MMMM YYYY")
Month ID = 0 -INT(FORMAT('Dates'[Date],"YYYYMM"))
Year = YEAR('Dates'[Date])
So I just take the Date table and relate it to the invoice date on my Sales data and I get this. Nice... but I want the graph to compare the data monthy. November 2015 next to November 2016, January 2016 to January 17.
By messing with some of the drill down settings. I can get it to look like this... but it's not in the correct order. November 2015 has to be first and so on.
Please let me know if you need anything else!
5 Replies
v-sihou-msftMicrosoft Employee
In your scenario, your first chart is taking the general calendar, as your data is started from Nov 2015. If you analysis on Month Name level (ignoring Year), like your second chart, it will take the general calendar sort for month number, that's the reason why it started from Jan.
For your requirement, you need to create the "custom sort" column to make Nov as the first. It can be like:
custom sort = IF ( 'Table'[Month Number] >= 11, 'Table'[Month Number] - 10, 'Table'[Month Number] + 2 )Then make the Month Name column "Sort By" above column.
Regards,
- JCBI1023
Hello v-sihou-msft,
Your column name in the picture says "month name" but your custom sort column says "month number". Do I have to create a customer month number column? How would that work? I created a custom month name column like you did:
Month Name = FORMAT ('Dates'[Date],"MMM")
- v-sihou-msft
Yes, you should have a Month Number column. You can just use MONTH() function on a Date column to get the month number.
Regards,
