cancel
Showing results for 
Search instead for 
Did you mean: 

Week Ending

Super User
1174 Views
Super User
Super User

Week Ending

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

 

 

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!