cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mquallet Frequent Visitor
Frequent Visitor

Showing Months in Chronological Order across Years

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

stacked.png

 

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?

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Showing Months in Chronological Order across Years

Hi @mquallet 

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.
Mariusz Repczynski

 

Super User
Super User

Re: Showing Months in Chronological Order across Years

@mquallet 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"))





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





8 REPLIES 8
Super User
Super User

Re: Showing Months in Chronological Order across Years

Hi @mquallet 

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.
Mariusz Repczynski

 

sean-owens Frequent Visitor
Frequent Visitor

Re: Showing Months in Chronological Order across Years

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!

Super User
Super User

Re: Showing Months in Chronological Order across Years

@mquallet 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.






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





mquallet Frequent Visitor
Frequent Visitor

Re: Showing Months in Chronological Order across Years

To add column, like so?    

o?stacked2.png

when i try it get error

stacked3.png

 

Super User
Super User

Re: Showing Months in Chronological Order across Years

@mquallet sorry that was DAX expression not power query. 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Super User
Super User

Re: Showing Months in Chronological Order across Years

@mquallet 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"))





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Super User
Super User

Re: Showing Months in Chronological Order across Years

Hi @mquallet 

 

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.
Mariusz Repczynski

 

mquallet Frequent Visitor
Frequent Visitor

Re: Showing Months in Chronological Order across Years

Creating the two new fields in PQ as such

 

solved.png

 

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

Thanks!

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 40 members 943 guests
Please welcome our newest community members: