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-03-2018 23:56 PM - last edited 06-26-2018 09:58 AM
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