Rolling Months to Display in Order

[luvagoldenk9]

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

[nahid3152]

HI,
Did u sort ur month column by using month number ?

[luvagoldenk9]

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.

[v-frfei-msft]

Hi @luvagoldenk9 ,

We can make the MMMYYYY column sorted by YYYYMM column as below.

For more details, please check the pbix as attached.

[luvagoldenk9]

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!

[v-frfei-msft]

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.

[luvagoldenk9]

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:

CALCULATETABLE(FILTER(DISTINCT(SELECTCOLUMNS('Source Table',"Keep",'Source Table'[Keep],"MMMYYYY",'Source Table'[MMMYYYY])),NOT(ISBLANK([Keep]))),FILTER('Source Table','Source Table'[Keep]="Keep"))

Do you know if the Sort Column functionality not work with calculated columns? 

Thank you for your input!

[luvagoldenk9 · Accepted solution]

@v-frfei-msft 

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!