Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX CALENDARAUTO()

HI   This may just seem like a simple solution, I maybe am over complicating matters here, but what I think makes sense DAX just disagrees or does the opposite to what I want.  I am a litte lost.  ...
DAX CALENDARAUTO()
  • lbendlin's avatar
    lbendlin
    6 years ago

    Looks good.  You can simplify the switch statements to either 

     

    SWITCH (TRUE(),[MonthNumber] in {11,12,1},"Q2"
,[MonthNumber] in {2,3,4},"Q3"
,[MonthNumber] in {5,6,7},"Q4"
,[MonthNumber] in {8,9,10},"Q1"
,blank() )

     

     

     

     

    or 

     

     

    SWITCH ([MonthNumber],1,"Q2",2,"Q3",3,"Q3",4,"Q3",5,"Q4",6,"Q4",
7,"Q4",8,"Q1",9,"Q1",10,"Q1",11,"Q2",12,"Q2",blank() )

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi

 

I have a datasource; what I mean by a datasource is I have a table with many columns which cover various periods/ranges but I am only interested in two columns, which is my key or main focus was to try and use the FiscalYearEndMonth part along with some filtering. 

 

When use the CALENDARAUTO in its own I get years in the range from 1979 to 2056, when I am only needing to look at say 2010 to 2020.  Its picking up another set of dates that are not needed for this purpose or not needed for working out the Fiancial Years if that makes sense.  

 

I thought adding some additional filtering to the forumla would offer some flexability to the range I wanted to use.

 

Chris

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

You're better off supplying your own dates table where you can control the range and the fiscal logic.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    H

     

    Understand, I take it you mean alogn these lines -

     

    CALENDAR (
        DATE ( 2005, 1, 1 ),
        DATE ( 2015, 12, 31 )
    ) 
     
    I have taken this example which is from the dax.guide site.
     
    Chris 

     

     

     

     

     

     

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      6 years ago

      Either that (and ADDCOLUMNS()) , or a totally external table that comes from SQL server, Excel, or even a CSV.

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        6 years ago

        Looks good.  You can simplify the switch statements to either 

         

        SWITCH (TRUE(),[MonthNumber] in {11,12,1},"Q2"
,[MonthNumber] in {2,3,4},"Q3"
,[MonthNumber] in {5,6,7},"Q4"
,[MonthNumber] in {8,9,10},"Q1"
,blank() )

         

         

         

         

        or 

         

         

        SWITCH ([MonthNumber],1,"Q2",2,"Q3",3,"Q3",4,"Q3",5,"Q4",6,"Q4",
7,"Q4",8,"Q1",9,"Q1",10,"Q1",11,"Q2",12,"Q2",blank() )