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

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

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