Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
04-19-2018 08:41 AM - last edited 06-27-2018 10:58 AM
Both the measure and column formulas for "Day Number of Year" and "Working Day Number of Year" are presented here for your enjoyment.
The measure DayNoOfYear:
DayNoOfYear = DATEDIFF ( DATE ( YEAR ( MAX('Calendar'[Date])), 1, 1 ), MAX('Calendar'[Date]), DAY ) + 1
The measure WorkingDayNoOfYear:
WorkingDayNoOfYear = VAR myDate = MAX([Date]) VAR myYear = YEAR(myDate) VAR tmpCalendar = ALL('Calendar') VAR tmpCalendar1 = ADDCOLUMNS(tmpCalendar,"tmpWeekDay",WEEKDAY([Date],2)) VAR tmpCalendar2 = FILTER(tmpCalendar1,[tmpWeekDay]<6 && YEAR([Date]) = myYear) VAR tmpCalendar3 = ADDCOLUMNS(tmpCalendar2,"WorkingDayNoOfYear",COUNTROWS(FILTER(tmpCalendar2,[Date]<myDate))+1) RETURN MAXX(FILTER(tmpCalendar3,[Date]=myDate),[WorkingDayNoOfYear])
eyJrIjoiMTgwMjQxYzUtMjJlOC00YWU3LTk2NTgtZDI3ZjJkMWFhMWVjIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9