Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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:

     

    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's avatar
Anonymous
Not applicable
5 years ago

@

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?

v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
5 years ago

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:

 

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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:

     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.

    • v-deddai1-msft's avatar
      v-deddai1-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

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