Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 )
)
)
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.
If this solution works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
The calculated column works! THANK YOU PAT!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |