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

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 

       

      • JMAlloway's avatar
        JMAlloway
        Helper I
        6 years ago

        Great - everything works out!