Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
In Power BI, I have to display monthly totals in a clustered column chart for the past 12 rolling months. Today is Nov 2019, so I need to show totals from Nov 2018 thru Oct 2019. I have the source data table connected to the date table with YYYYMM (1:many relationship set between the two tables). The date table also contains a separate text field for 'Nov 2018'.
When I use the date table value YYYYMM in the visual, the sort order remains as it is ascending by value (201811, 201812, 201901, 201902, etc). However, I need to have the visual display the text field value of 'Nov 2018' and still maintain the sequence - Nov 2018, Dec 2018, Jan 2019, Feb 2019 ... Oct 2019. When I use the date text field it sorts the column chart in alphabetical sequence and loses the rolling 12 months' sequence.
Has anyone encountered this scenario or does anyone have any suggestions? Thank you in advance for your review and input. Much appreciated!!!
Solved! Go to Solution.
I copied the calculated field Date Table MMMYYYY to a another column in the table (called it 'Test') and used 'Test' in the visual. Quirky workaround but the sorting now works. Looks like the field connected to the table and used in the visual cannot be a calculated column to work.
Thank you for your input as that really helped to figure out the issue. Have a great day!
Hi,
Please let me clarify the data fields here.
The source data table and the date table are joined by a field MMMYYYY (i.e. Nov 2018, Dec 2018, etc.). The date table has another field defined with the corresponding YYYYMM values (201811, 201812, etc.).
DATE TABLE:
MMMYYYY (field connected to source data table) YYYYMM (sorted ascending)
Nov 2018 201811
Dec 2018 201812
Jan 2019 201901
Feb 2019 201902
The clustered column chart sorts perfectly when using the Date Table YYYYMM field as that is a numeric ascending order value. However, the visual needs to display the date field as MMMYYYY. When I use the MMMYYYY field in the visual, it sorts alphabetically even when the Date Table is sorted ascending on YYYYMM.
Thoughts? Thank you.
Hi @luvagoldenk9 ,
We can make the MMMYYYY column sorted by YYYYMM column as below.
For more details, please check the pbix as attached.
Hi @v-frfei-msft ,
Thank you for your response. When I tried that option (selecting MMMYYYY > click on Sort By Column > select YYYYMM), I received an error message:
Sort by another column
This column can't be sorted by a column that is already sorted,
directly or indirectly, by this column.
There are no sorts on either of these fields in the Date Table so not sure what is being interpreted. There are only 12 rows in the Date Table. MMMYYYY is connected by a 1:many relationship to Source Data Table:
MMMYYYY YYYYMM
Nov 2018 201811
Dec 2018 201812
Jan 2019 201901
Feb 2019 201902
Mar 2019 201903
Apr 2019 201904
May 2019 201905
Jun 2019 201906
Jul 2019 201907
Aug 2019 201908
Sep 2019 201910
Oct 2019 201911
Would I have to split out YYYYMM into a separate table for the sort to work?
Thoughts! Thank you again!
Hi @luvagoldenk9 ,
Are they calculated columns? Kindly share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Hello @v-frfei-msft ,
The Source Table has a field MMMYYYY and a field called 'Keep' which indicates whether or not the MMMYYYY is in the past rolling 12 months.
MMMYYYY Keep
Oct2015
Dec2018 Keep
Sep2016
May2019 Keep
The Date Table MMMYYYY is a calculated column based on data from the Source Table:
I copied the calculated field Date Table MMMYYYY to a another column in the table (called it 'Test') and used 'Test' in the visual. Quirky workaround but the sorting now works. Looks like the field connected to the table and used in the visual cannot be a calculated column to work.
Thank you for your input as that really helped to figure out the issue. Have a great day!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |