cancel
Showing results for 
Search instead for 
Did you mean: 

Day Number of Year and Working Day Number of Year

Super User
486 Views
Super User
Super User

Day Number of Year and Working Day Number of Year

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])

 

 

 


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

Proud to be a Datanaut!