Show 6 months of data, total the rest

[gdaniel]

Hello, 

I need to create a Matrix report that show 6 months of data, then totals everything beyond 6 months.  

I have sucessfully created a calculated column that does this.  

_MonthYear =

IF('Calendar'[Date] >= EOMONTH(TODAY(), 6), "Beyond 6 Months",   'Calendar'[MonthYear])

It works, but displays the months in alphabetical order, not chronological.  

I need to figure out either 1) how to sort the _MonthYear column chronologically, or 2) how to hide columns greater than 6 months out but still keep the Total column.  

The error I get when trying to sort the _MonthYear column is this:

Appreciate any help!

[techies]

Hi @gdaniel please try this

In Power Query, add an Index Column to your Calendar table and name it _MonthYearIndex.
In Data View, select _MonthYear and sort it by _MonthYearIndex.

[vicky_ · Accepted solution]

To sort chronologically, you need to create a new integer column. This could be something like:

Month Sort Column = SWITCH('Calendar'[MonthYear], "Beyond 6 Months", 9999999, FORMAT('Calendar'[MonthYear], "yyyymm") // e.g. Apr-2025 should give you 202504

then select your original Month-Year column and sort using the above.

Otherwise, you could try something like:

Table Measure = IF(HASONEVALUE(Calendar[MonthYear]), [Your normal calculation], [Your calculation for the total])

[gdaniel]

Thanks @vicky_ ,  I created a column like this, and it works to create the correct data in the column:  

_Sort2 = IF(

    'Calendar'[Date] >= EOMONTH(TODAY(), 6), "999999",

        FORMAT( 'Calendar'[Date], "YYYYMM"))

However, when I try to sort my _MonthYear column with this new _Sort2 column, I get a very unhelpful error message  😞 

I will keep working on this, and post a solution if I find one.

[gdaniel]

Update - the sort column solution above worked!  I researched the validation error message I received, and was able to resolve it by doing this:  

Verify whether a program is causing the issue by closing Power BI Desktop, opening Windows Settings > About > Advanced system settings and selecting Environment Variables. Select New under User variables and add variable name WEBVIEW2_ADDITIONAL_BROWSER_ARGUMENTS with the value --disable-features=RendererCodeIntegrity.

Thanks again @vicky_