topic Re: Show 6 months of data, total the rest in DAX Commands and Tips

Show 6 months of data, total the rest

gdaniel — Wed, 12 Mar 2025 19:26:35 GMT

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!

Re: Show 6 months of data, total the rest

vicky_ — Wed, 12 Mar 2025 21:32:27 GMT

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])

Re: Show 6 months of data, total the rest

techies — Thu, 13 Mar 2025 09:35:53 GMT

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.

Re: Show 6 months of data, total the rest

gdaniel — Thu, 13 Mar 2025 15:16:48 GMT

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.

Re: Show 6 months of data, total the rest

gdaniel — Fri, 14 Mar 2025 12:30:35 GMT

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_