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() )

     

     

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() )

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Ibendin

         

        Thats great, will give that a trial also.

         

        Thanks again.

         

        Chris

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Hi lbendin
       
       
       
       
       
       
       
       
       
       
       

       

      If you are ok to continue with this topic, I have updated my model from your last reply, or would you prefer this discussion is now closed?

       

      So I took on board what you said about ADDCOLUMNS, I don't have the option of a date table from SQL or excel so I would be creating a version in my model.  Below is what I have compiled, I will admit I have taken a few snippites from various sources, some examples out there are just too in depth and I would not fully understand what I was using.  Hopefully what I am doing here is still considered to be good practice - 

       

      So I applied just some small amounts of DAX to get the results in my 'dimDate' table (shown below as a screen print).

       

      Dax:

      dimDate =
      ADDCOLUMNS(
      ADDCOLUMNS(
      CALENDAR(
      DATE(2000,1,1),
      DATE(2050,12,31)
      ),
      "Year", year ([Date]),
      "MonthNameLong", FORMAT([Date],"mmmm"),
      "MonthNameShort", FORMAT ( [Date], "mmm" ),
      "MonthNumber", MONTH ( [Date] )
       
      ),
      "FY", IF ( [MonthNumber] >= 8, [Year] + 1, [Year] ),
      "FY_Qtr",
      SWITCH (TRUE(),
      [MonthNumber] = 1, "Q2",
      [MonthNumber] = 2, "Q3",
      [MonthNumber] = 3, "Q3",
      [MonthNumber] = 4, "Q3",
      [MonthNumber] = 5, "Q4",
      [MonthNumber] = 6, "Q4",
      [MonthNumber] = 7, "Q4",
      [MonthNumber] = 8, "Q1",
      [MonthNumber] = 9, "Q1",
      [MonthNumber] = 10, "Q1",
      [MonthNumber] = 11, "Q2",
      [MonthNumber] = 12, "Q2",
      blank() ),
      "FY_Period",
      SWITCH (TRUE(),
      [MonthNumber] = 1, "6",
      [MonthNumber] = 2, "7",
      [MonthNumber] = 3, "8",
      [MonthNumber] = 4, "9",
      [MonthNumber] = 5, "10",
      [MonthNumber] = 6, "11",
      [MonthNumber] = 7, "12",
      [MonthNumber] = 8, "1",
      [MonthNumber] = 9, "2",
      [MonthNumber] = 10, "3",
      [MonthNumber] = 11, "4",
      [MonthNumber] = 12, "5",
      blank() )
      )
      dimDate table generated from code above.
      dimDate
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
      Many thanks
      Chris