Showing Months in Chronological Order across Years

[Anonymous]

Hello

Simple issue but devilish:  I have multiple year data with a simple 'create date' field in it for each record.

For one view I'd like to show the data in chronological order in a simple stacked chart, but when i span multiple years it shows months in year order (Jan - Dec) and just puts the data from each month side by side

Ie. if i show July 18 - July 19 it starts with Jan 19, Feb 19....July 18/July 19, Aug18/Aug 19, etc.....

I would like to show earliest date to current date (true chronological order)

July 18, Aug 18, Sept 18, ..... Jan 19, Feb 19, ,....July 19

so the above chart would START with July 18 (orange) and proceed through chronologically to July 19 (DkBlue)

Any ideas or visuals that would assist?

[Mariusz · Accepted solution]

Hi @Anonymous 

Please watch the video in the link below.

https://www.youtube.com/watch?v=NlX7Cp2P0d4

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[parry2k]

@AliceW Glad, it helped and worked out for you. Cheers!!

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Creating the two new fields in PQ as such

and then selecting the field and Modeling and sorting by the new sort field worked.

Thanks!

[parry2k]

@Anonymous you need to add columns in your table to achieve this

Month Year = FORMAT( Table[Date], "MMM-YY" )

Month Year Sort = FORMAT( Table[Date], "YYYY-MM" )

Selected your Month Year column, go to modelling tab and select sort by option from menu and choose Month Year Sort

After all above done, use Month Year on x-axis and year on legend and you will get the result.

[AliceW]

Parry, I'm a bit late with this reply, but it was a life-saver! Thanks, man.

[Anonymous]

To add column, like so?    

o?

when i try it get error

[Mariusz]

Hi @Anonymous 

M expression would look like below.

Number.ToText( Date.Year([Created])) & " " & Text.Start(Date.MonthName([Created]), 3)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[parry2k]

@Anonymous sorry that was DAX expression not power query. 

[parry2k · Accepted solution]

@Anonymous if you want to use power query to add columns, you following syntax

 Month Year = Date.ToText([Date],"MMM-yy"))

Month Year Sort =  Date.ToText([Date],"yyyy-MM"))

[Anonymous]

Hi there!

In the reports I've been working on, I have come upon this issue, and I think my solution is pretty reliable.

I used a DAX formula to generate a custom column that concatenates the year and the month into a number as follows:

usageDate = Table[Date].[Year] & FORMAT(Data[Date].[Month], "00")

So now usage date looks something like "201904" or "201912". From there, you may need to create another custom column that has both the month name and the year in it. I don't think you will be able to just sort a column of months with my following method since there will be repeats in the month column otherwise.

Go to modeling, click Sort by Column, and select the new usageDate column. As a result, the Month-Year column (which you will have to create a custom column for) will now sort by the usageDate which is effectively just a number that increases as months and years go on.

Hope this helps!

[Anonymous]

Thank you @Anonymous , I think this'd work for me too! 

I've made my own solution, but came to look for other solutions, since I was pretty sure it's not the optimal one. I made a copy of the year column, squared it and then made a new column with year(square)+monthnumber. So for example the values for Oct 2018 to Mar 2019 are 

Oct 2018: 4 072 334

Nov 2018: 4 072335

Dec 2018: 4 072 336

Jan 2019: 4 076 362

Jan 2019: 4 076 363

Jan 2019: 4 076 364

After this I can use this new column to rank the order of the months, even if there's information divided montly from multiple years. It works, but is a bit crude hack. 

[Mariusz · Accepted solution]

Hi @Anonymous 

Please watch the video in the link below.

https://www.youtube.com/watch?v=NlX7Cp2P0d4

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.