Forum Discussion
Modifying Date_Calendar DAX
Hi, I inherited a PBIX file which has a Date_Calendar table created with the DAX code below.
It works fine, except I need to make 2 changes to it and would be grateful for the help.
1) Can you modify the DAX to add a Week field; and it would not be Week number but rather start of week date in this format (mm/dd/yyyy), e.g. 4/27/2020, 5/4/2020, 5/11, 2020, etc.
2) Can you modify the DAX so that the start of the week is a Monday, i.e. 4/27/2020, 5/4/2020, 5/11/2020, etc.
THANK YOU!!!!!!!!!!!!!!!!!!!!
Date_Calendar =
GENERATE (
CALENDAR(Date(2015,7,1),Date(2023,06,31)),
VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday
VAR currentDay = [Date]
VAR days = DAY( currentDay )
VAR months = MONTH ( currentDay )
VAR years = YEAR ( currentDay )
VAR nowYear = YEAR( TODAY() )
VAR nowMonth = MONTH( TODAY() )
VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
VAR todayNum = WEEKDAY( TODAY() )
VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
VAR fiscalyear = CONCATENATE("FY", IF(MONTH(currentDay) <=6, VALUE(FORMAT(currentDay,"YY")), VALUE(FORMAT(currentDay,"YY")) +1 ) )
VAR fiscalfullyear = IF(MONTH(currentDay) <=6, VALUE(FORMAT(currentDay,"YYYY")), VALUE(FORMAT(currentDay,"YYYY")) +1 )
VAR fiscalmonthOLD = CONCATENATE( SWITCH(month(currentDay),1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December") , fiscalfullyear )
VAR fiscalmonth = CONCATENATE( SWITCH(month(currentDay),1,"January ",2,"February ",3,"March ",4,"April ",5,"May ",6,"June ",7,"July ",8,"August ",9,"September ",10,"October ",11,"November ",12,"December ") , years )
VAR fiscalquarter = CONCATENATE( fiscalyear , SWITCH(month(currentDay),7,"-Q1",8,"-Q1",9,"-Q1" ,10,"-Q2",11,"-Q2",12,"-Q2" ,1,"-Q3",2,"-Q3",3,"-Q3" ,4,"-Q4",5,"-Q4",6,"-Q4", blank() ) )
RETURN ROW (
"Fiscal Quarter", fiscalquarter,
"Fiscal Full Year", fiscalfullyear,
"Fiscal Year", fiscalyear,
"day", days,
"month", months,
"year", years,
"Fiscal Month", fiscalmonth,
"fiscalmonthOLD",fiscalmonthOLD
// "day index", dayIndex,
// "week index", weekIndex,
// "month index", INT( (years - nowYear ) * 12 + months - nowMonth )
// "year index", INT( years - nowYear )
)
)
2 Replies
- mahoneypatMicrosoft Employee
I think you just need to change your start of week variable to this
VAR startOfWeek = 2
The other parts of the expression calculate off of that. The other option is to add a calculated column to your Date table using a formulat like this to get the Monday for each Date's week.
MondayWeek = Date_Calendar[Date]+2-WEEKDAY(Date_Calendar[Date])If this solution works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
- AnonymousNot applicable
The calculated column works! THANK YOU PAT!