Showing results for 
Search instead for 
Did you mean: 

Week Starting

Super User
Super User
Super User

Week Starting

Produce a user friendly week identifier such as:


W12 Week Starting 3/25/2018

W13 Week Starting 3/30/2018

W14 Week Starting 4/8/2018


Also the PBIX makes slight improvements to Week Ending: to remove hard-coded date values from temporary calendar table and clean up some extraneous CALCULATE functions. PBIX also includes column versions of both measures.



mWeekStarting = 
//Get information about the current date
VAR myDate = MAX(Calender[Date])
VAR myWeekNum = WEEKNUM(myDate)
VAR myYear = YEAR(myDate)
//Set min and max for Calendar table
VAR minDate = CALCULATE(MIN(Calender[Date]),ALL(Calender))
VAR maxDate = CALCULATE(MAX(Calender[Date]),ALL(Calender))
//Create calendar table and add required columns
VAR dateTable = CALENDAR(minDate,maxDate)
VAR dateTable1 = ADDCOLUMNS(dateTable,"WeekNum",WEEKNUM([Date]))
VAR dateTable2 = ADDCOLUMNS(dateTable1,"WeekDay",WEEKDAY([Date]))
//Return date that matches the current year and weeknum and is a week day of 1 (Sunday)
VAR weekStartDate = MINX(FILTER(dateTable2,YEAR([Date])=myYear&&[WeekNum]=myWeekNum&&[WeekDay]=1),[Date])
//If null, then it is at the end of the year and the week starts last year - there may be 53 weeks that year
VAR weekStartDate1 = IF(NOT(ISBLANK(weekStartDate)),weekStartDate,MINX(FILTER(dateTable2,YEAR([Date])=myYear-1&&[WeekNum]=53&&[WeekDay]=1),[Date]))
//If null, then it is at the end of the year and the week starts last year - there may not be 53 weeks that year
VAR weekStartDate2 = IF(NOT(ISBLANK(weekStartDate1)),weekStartDate1,MINX(FILTER(dateTable2,YEAR([Date])=myYear-1&&[WeekNum]=52&&[WeekDay]=1),[Date]))
//If it is still null, then it is at the start of our calendar table
VAR weekStartDate3 = IF(NOT(ISBLANK(weekStartDate2)),weekStartDate2,MINX(dateTable2,[Date]))
//Return a nicely formatted week ending format "W# Week Ending mm/dd/yyyy"
RETURN "W" & myWeekNum & " Week Starting " & weekStartDate3







Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!