DAX CALENDARAUTO()

[Anonymous]

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. 

This is my forumla in DAX, I am trying to create a Date table, which will evetually will be populated with our Fiscal Periods and Quarters.  I thought if I could first get the key Date column right then the rest would just fall into place; thats the theory.

My Formula:

Date = 

VAR MinYear = Year( min (Details[Submitted_Date] ))

VAR MaxYear = Year( max (Details[End_Date] ))

RETURN

FILTER (

CALENDARAUTO(7),

YEAR ( [Date]) >= MinYear &&

YEAR ( [DATE] ) <= MaxYear

)

Now I wanted my Fiscal year, or the FiscalYearEndMonth to be July.  When I use 'CALENDARAUTO(7)'  as is, the dates are great but the reason I am using the min and max is to reduce or keep my table size down, focus only on the date I need.  You see the number 7, in RED, the formula or calucation ignores this Parameter but I am not sure why.  When I run the full forumal the date starts in January not August. 

Any help greatly apprciated.

Chris

[lbendlin]

That's a rather inventive use of CALENDARAUTO.  Have you considered using CALENDAR() and then just adding your own Fiscal Year logic?  Or even better, use an external Dates table.

[Anonymous]

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]

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

[Anonymous]

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]

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

[lbendlin · Accepted solution]

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]

Ibendin

Thats great, will give that a trial also.

Thanks again.

Chris

[Anonymous]

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