Forum Discussion

JMAlloway's avatar
JMAlloway
Helper I
6 years ago
Solved

Custom Filtered Individual Columns

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'.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      JMAlloway
      Helper I
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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