DayNoOfYear is a fairly straightforward calculation. We simply use the DATEDIFF function to return the number of DAY intervals between our current working date and January 1 of the same year. We then simply need to add 1 to this number so that January 1 does not end up being zero. DATEDIFF can return differences between dates in the following increments:
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
WorkingDayNoOfYear is relatively more complex than DayNoOfYear. We begin by getting the current date and the year of that date and storing those values in the variables, __Date and __Year. Next, we create a variable that stores a temporary table called __Calendar, which contains all dates within our date table filtered to our current working year. We then use the ADDCOLUMNS function to add a __Weekday column to the __Calendar table using the WEEKDAY function and return the resulting table in the __Calendar1 variable. It should be noted that the second parameter for the WEEKDAY function can be 1, 2, or 3.
This translates into the following return values:
- 1: The week begins on Sunday (1) and ends on Saturday (7).
- 2: The week begins on Monday (1) and ends on Sunday (7).
- 3: The week begins on Monday (0) and ends on Sunday (6).
Once we have our __Weekday column in __Calendar1, we can then use FILTER on __Calendar1 to only include weekdays (__Weekday values less than 6) and return the result in the __Calendar2 variable. We can then create our final temporary table, __Calendar3, by using __Calendar2 and adding a column using ADDCOLUMNS to create a __WorkingDayNoOfYear column. This column is created by counting the number of rows (+ 1) in the table where the Date is less than our current working date (__Date). It may seem like an odd construct to be adding a column to a table variable while, at the same time, basing the value of the column on counting a filtered subset of that same table variable, but this is perfectly legal in DAX!
Once we have the final __Calendar3 table, we can return the desired value by filtering this table down to the current date we are working with and wrapping an iterative aggregation function around the result. In this instance, we chose MAXX, but we could just as easily have chosen MINX, SUMX, or AVERAGEX, since we are filtering down to only a single value.