Forum Discussion

esterdid's avatar
esterdid
Frequent Visitor
8 years ago
Solved

Date calendar dax code

Hello,   I am completly new to Dax and will appreciate any assistance here. I have come across this dynamic calendar based on DAX and Ifound it very simple to use. the issue is that I need to expan...
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi esterdid

     

    I think this might be close 

     

    Dates = 
    VAR BaseCalendar =
    CALENDAR(date(2016,1,1),today())
    RETURN
    GENERATE (
    BASECALENDAR,
    VAR BASEDATE = [DATE] VAR DayDate = DAY([DATE])
    VAR YEARDATE = YEAR ( BASEDATE )
    VAR MONTHNUMBER = MONTH ( BASEDATE )
    VAR YEARMONTHNUMBER = YEARDATE * 12 + MONTHNUMBER -1
    VAR FINANCIALQTR = SWITCH(
                            TRUE(),
                            MONTH([Date]) IN {7,8,9} , 1 ,
                            MONTH([Date]) IN {10,11,12} , 2 ,
                            MONTH([Date]) IN {1,2,3} , 3 ,  
                            4)
    VAR FINANCIALYR = IF( MONTH([Date]) >=7 , YEARDATE , YEARDATE - 1)
    RETURN ROW (
    "DAY", DayDate,
    "YEAR", YEARDATE,
    "MONTH NUMBER", MONTHNUMBER,
    "MONTH", FORMAT ( BASEDATE, "MMMM" ),
    "YEAR MONTH NUMBER", YEARMONTHNUMBER,
    "YEAR MONTH", FORMAT ( BASEDATE, "MMM YY" ),
    ------------------------------
    "Week Number",WEEKNUM([Date]),
    "Week Ending",[Date] + (6-(WEEKDAY([Date],3))),
    "Day Name" , FORMAT([Date],"DDDD"),
    "CalendarQtr" , INT(MONTH([Date]) / 4) + 1 ,       
    --
    "FinancialQtr" , "FYQ" & FINANCIALQTR ,
    "FinancialYr" ,"FY" & FINANCIALYR,
    "FinancialYrQtr" ,  FINANCIALQTR &  FINANCIALYR             
    
    )
    )