Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
Solved! Go to 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:
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 ,
Try have a column like same without a sort order
month year = format([Date], "mmm-yyyy")
do not sort this month year sort
@amitchandak
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:
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
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 amitchandak) 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.
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
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |