Forum Discussion
esterdid
8 years agoFrequent Visitor
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...
- 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 ) )
esterdid
8 years agoFrequent Visitor
It starts in July (07). Thanks
Phil_Seamark
8 years agoMicrosoft Employee
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
)
)- esterdid8 years agoFrequent Visitor
Thanks Phil.
- powerfuldan7 years agoNew Member
Hi Phil - Could you give syntax for calculating fiscal month number, if my fiscal month is starting in April?