Week Ending

Greg_Deckler — Tue, 26 Jun 2018 16:58:05 GMT

Produce a user friendly week identifier such as:

W12 Week Ending 3/31/2018

W13 Week Ending 4/7/2018

W14 Week Ending 4/14/2018

mWeekEnding = 
//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 maxDate = DATE(2017,1,1)
VAR minDate = DATE(2018,12,31)
//Create calendar table and add required columns
VAR dateTable = CALENDAR(maxDate,minDate)
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 7 (Saturday)
VAR weekEndDate = CALCULATE(MAXX(FILTER(dateTable2,YEAR([Date])=myYear&&[WeekNum]=myWeekNum&&[WeekDay]=7),[Date]))
//If null, then it is at the end of the year and the week ends next year
VAR weekEndDate1 = IF(NOT(ISBLANK(weekEndDate)),weekEndDate,CALCULATE(MAXX(FILTER(dateTable2,YEAR([Date])=myYear+1&&[WeekNum]=1&&[WeekDay]=7),[Date])))
//If it is still null, then it is at the end of our calendar table
VAR weekEndDate2 = IF(NOT(ISBLANK(weekEndDate1)),weekEndDate1,MAXX(dateTable2,[Date]))
//Return a nicely formatted week ending format "W# Week Ending mm/dd/yyyy"
RETURN "W" & myWeekNum & " Week Ending " & weekEndDate2

eyJrIjoiYjAwYzZjZTEtYTUxMy00OGEzLWI3OTMtYTY3NmNkMjE5ZGNiIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

topic Week Ending in Quick Measures Gallery

Week Ending