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])
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
