Custom Mixed-Period Calendar Column

[alann]

Hi Everyone, This one is driving me mad but hoping someone with a bit more experience than me can assist.

I have a simple calendar table (not truely this simple, but for the context of this forum post) which is indicated as grey in the table below.

I want to add a new column which, depending on the MAX([Month_Number]), will display either the [Month_Name] or [Quarter] depending if the MAX([Month_Number]) <= max for the quarter.

The resulting column will be used in a 'Profit & Loss Statement' chart i have running as a 'Matrix' object and would display (under the scenarios above) as below

Scenario of Max ( [Month_Number] ) = 8

Scenario of Max ( [Month_Number] ) = 5

Currently using the Matrix and using Quarter and Month_Name as the Columns it displays as below and there is no way to colapse a single Quarter column whlst leaving the other Quarter column expanded. (unless im doing something wrong)

Currently my IF statement reads as follows, but i'm unsure how to not have calculate against each line but rather over every line.

Thanks in advance.

[alann]

An update on this,

I have ended resolving this by creating a second table, linked to the Calendar table, with the static combinations of column sets based on the selected month. The month selection is now may on this secondary table, rather than the 'Calendar' table itself.

In this way when i select 'Jul' in the [Slicer Option] it filters for months Apr, May, Jun and Jul and i use the [Column_Set] field as the column in my matrix table. (see output below)

If anyone has any "smarter" solutions that wouldn't require a secondary static table please let me know else this is solved for now.

Thanks.

[AlejandroAldama]

A column within a dimension table that dinamically changes its values with a slicer is not possible. 
A solution would be to add a slicer with the month column you are using in your matrix, this should allow you to select the range of months you want to visualize.

Also a slicer for year is necessary, otherwise the matrix will show the cumulative data for the past years in the selected months.

When creating the matrix it would be better if you put Quarters and Months in Rows so that you do not have any problem when drilling down.

For example in the table below the month slicer is set to 5 and the year slicer is set to 2003 with some random data.

As you can see you get the total for the preceding quarter as well as the data for the rest of the months indicated in the slicer. 
If you strictly need Quarters and Months in Columns, the matrix will looke like this.

You still have the data from the previous matrix, but with additional columns, which can be problematic in case you need to analyze data up to the november, as this can occupy more space in your report than you would like to.

I hope this helps, greetings. 

[alann]

Thanks for your response @AlejandroAldama 

Unfortunately for Financial Statements putting the date dimension as the rows isn't an option. Thats completely no-standard and against international financial reporting conventions so simply not an option.

The issue with your last image of quarters and months in columns is when we have Actual, Budget, Variance and YTD or Prior Year sets of values under each date "bucket" you end up with, for instance when you get to October (month 10), 43 columns in the report which is just crazy. If the column sets instead could be: Q1, Q2, Q3 and October, then it's trimmed down to just 16 columns which, whilst still alot, fits on a landscae printed page atleast.

I kept my example of Revenue, COGS and Gross Profit overly simple to not complicate this request for help. In reality the financial statement is approx. 150 rows in lengh. To give you a larger view of my report, see example below. Note: 1. only up to month 4 and already lots of columns. What i want is Apr/May/Jun collapsed into a Q1 total and then Jul showing as is. So basically two "date buckets"/column sets 'Q1' and 'Jul'

I'm wondering if I need a seperate, with no relationship links, table to set up for these groupings and even a seperate DAX measures table for the eventual Actual, Budget, Variance, Prior Year/YTD formuals.

Thanks,