Modifying Date_Calendar DAX

[Anonymous]

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 )

  )

)

[mahoneypat]

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

[Anonymous]

The calculated column works! THANK YOU PAT!