A shorter formula for DayNoOfYear exists that demonstrates a useful trick for dates but can somewhat obfuscate what is going on and therefore be harder to read and decipher. To use this technique, create a new column in the R04_Calendar table with the following formula:
DayNoOfYear2 =
('R04_Calendar'[Date] - DATE(YEAR('R04_Calendar'[Date]), 1, 1 )) * 1 + 1
Instead of using DATEDIFF, we can simply subtract the dates from one another to get the number of days. This is because a date is really just a decimal number where the whole number portion is the number of days since December 30, 1898. The decimal portion is the time component as a fraction of the day.
Since we are dealing with pure dates where the time is essentially midnight (12:00:00 AM), we do not have to worry about the time component because 0 – 0 = 0. Thus, when we subtract dates, we get back the number of days between the two dates.
However, because we are subtracting dates, DAX believes that we want a date returned from the calculation. If we did not take steps to compensate, we would end up with a date that was, however, many days returned from the subtraction away from December 30, 1899 which is most definitely not what we want!
To avoid getting back an obscure date, we can wrap our date subtraction in parentheses and multiply by 1. Multiplying by 1 forces DAX to understand that we desire a number from our calculation instead of a date. We then just need to add 1 to the result of this calculation for the same reason as in DayNoOfYear.