Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
04-06-2018 15:32 PM - last edited 06-27-2018 11:10 AM
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