Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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.
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!
Solved! Go to 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])
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.
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])
Thanks @vicky_ , I created a column like this, and it works to create the correct data in the column:
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.
Update - the sort column solution above worked! I researched the validation error message I received, and was able to resolve it by doing this:
Thanks again @vicky_
User | Count |
---|---|
12 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
19 | |
14 | |
10 | |
7 |