Week Starting

Greg_Deckler — Wed, 27 Jun 2018 18:10:28 GMT

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: https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Ending/m-p/389293 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

eyJrIjoiNDEwY2M1ZmYtYjM5ZC00NDkwLTliMjEtMjVkNmMyMzhlODRlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

topic Week Starting in Quick Measures Gallery

Week Starting