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. 

 

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

     

     

8 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    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'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
        Super User
        6 years ago

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