Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Combined chart with non chronological X axis

Hi everybody.

I need Your help with combined chart. Bar chart presents number of customers, line chart presents sales. On X axis I have dates arranged chronologically (Jan 2019, Feb 2019, March 2019... Jan 2021, Feb 2021, March 2021 etc.). Is it possible (and how to do this) to rearrange dates on X axis to obtain non chronological layout (I mean: Jan 2019-> Jan 2020 -> Jan 2021, then Feb 2019 -> Feb 2020 -> Feb 2021 etc)?

I would appreciate Your help 🙂

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

You can create a date table in your data model like:

 

Table = ADDCOLUMNS(CALENDAR(DATE(2019,1,1),DATE(2021,12,31)),"monthYear",FORMAT([Date],"mmm")&YEAR([Date]),"sortvalue",MONTH([Date])+YEAR([Date])/10000)

 

Then sort the monthyear column by sortvalue column:

Capture18.PNG

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@Anonymous ,

Try have a column like same without a sort order

month year = format([Date], "mmm-yyyy")


do not sort this month year sort

Anonymous
Not applicable

@

It kinda helped - I sorted data by month-year on chart and this returned me X axis arranged like:

Jan 2021->Jan 2020->Jan 2019

then March 2021->March 2020->March 2019

then May 2021->May 2020->May 2019

then Feb 2021->Feb 2020->Feb 2019

etc...

I know my boss and he'll ask to get months in chronological order, not shifted...

Any idea how to cope with this?

Hi @Anonymous ,

 

You can create a date table in your data model like:

 

Table = ADDCOLUMNS(CALENDAR(DATE(2019,1,1),DATE(2021,12,31)),"monthYear",FORMAT([Date],"mmm")&YEAR([Date]),"sortvalue",MONTH([Date])+YEAR([Date])/10000)

 

Then sort the monthyear column by sortvalue column:

Capture18.PNG

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

Anonymous
Not applicable

Sorry, I still do not understand what I have to do.... I'm not as fluent in PowerBI as most of members... 😞

What for is the date table? I tried to create this table, but still I have to connect it with sales data in order to achieve combined chart with X axis arranged in order I mentioned (Jan 2019, Feb 2019, March 2019... Jan 2021, Feb 2021, March 2021 etc.). For now I have solution with additional column (proposed by ) and (after sorting) mixed dates instead of chronological as I need:

powerbi.PNG

 As Ypu can see on picture, on my X axis I have June 2019 -> June 2020 -> June 2021, then December 2019 -> December 2020 then April 2019 -> April 2020 -> April 2021, then July 2019 -> July 2020 etc.

Hi @Anonymous ,

 

If you use the monthYear column from the new date table as x-axis, it will show as your expected.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

Anonymous
Not applicable

Sorry, but if I use the monthYear column this gives nothing but mess. Even if I create relation between date table and sales table the result is still the same: on my X axis I have June 2019 -> June 2020 -> June 2021, then December 2019 -> December 2020 then April 2019 -> April 2020 -> April 2021, then July 2019 -> July 2020 etc.

I do something wrong, but what...

Hi @Anonymous ,

 

How did you create relationship between date table and sales table? By date column? Would you please show us sample pbix file then I can find out where you did wrong.

 

Best Regards,

Dedmon Dai

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.