Custom Filtered Individual Columns

[JMAlloway]

Hello All - 

I am looking to create a clustered column chart that shows data from Jan 2017- Dec 2019. I would like to show each year individually by quarter, however, if it is the current quarter, drilldown to months. I already have a date table, but I'm having difficulty figuring out how to write out a DAX argument that groups dates by quarter, except for the current quarter-- show months. Any ideas? Referring to the image below, 'Qtr 4' will need to be broken down into 'October', 'November', and 'December'.

[Anonymous · Accepted solution]

Hi @JMAlloway ,

Try the following: I added two (DAX) columns to my Date table using the following DAX: 

DateAxisValue = 
VAR CurrentQuarter = 4
// QUARTER ( TODAY ())
Return
IF (
QUARTER('Date'[Date]) = CurrentQuarter,'Date'[Month],"Q" & QUARTER('Date'[Date])
)

DateAxisValueSort = 
VAR CurrentQuarter = 4
// QUARTER ( TODAY ())
Return
IF (
QUARTER('Date'[Date]) = CurrentQuarter, "Q" & QUARTER('Date'[Date]) & 'Date'[Month Number],"Q" & QUARTER('Date'[Date])
)

Make sure you change the Sort Column for the DateAxisValue Column to DateAxisValueSort

Then you should be able to produce something like this:

 

The CurrentQuarter is hardcoded to 4 in the sample, but can also be based current date. Be aware that when the column is calculated there is no filter context active

Jan
if this is a solution for you, don't forget to mark it as such. thanks 

[JMAlloway]

Hi Jan -

Thanks for the reply. This looks great! My one issue, is that when enter "QUARTER" on line 6, it's telling me that "The funding used in the expression is not a valid function nor the name of a measure". I currently have a Quarter column in my date table. Do you know what could be wrong?

Thanks!

JMA

[Anonymous]

Hi @JMAlloway , 

I think it is a pretty new function (in DAX). I haven't found it yet in the MSDN, but it is in this guide https://dax.guide/quarter/

Otherwise there are various ways to calculate the quarter: 

VAR quarterNumber = ISO.CEILING(MONTH([Date])/3,1)  or  VAR quarterNumber = ROUNDUP(MONTH([Date])/3,0)

These are still usefull since the QUARTER does not take financial years into account. 

Jan 

[JMAlloway]

Great - everything works out!