Need day number to be different for different years

[ClaireN]

Hi everyone

I need to compare the numbers of samples arriving at a lab by day - and compare 2019 to 2020. But need the days of the week to line up - 

DayNoOfYear =
DATEDIFF ( DATE ( YEAR ( 'Table'[Date] ), 1, 1 ), 'Table'[Date], DAY ) + 1

that formula is all good, but I need day 1 of 2020 (a wednesday) to have the number 2 against it so that it matches with the first day Wednesday in 2019, which is a 2 (day 1 of 2019 is a Tuesday). 

Is it possible to modify that formula so that if year = 2020 I can make it be +2 at the end instead of +1 in my date table?

Hope all are well 

Claire

[Greg_Deckler]

Perhaps something like:

DATEDIFF ( DATE ( YEAR ( 'Table'[Date] ), 1, 1 ), 'Table'[Date], DAY ) + 1 + WEEKDAY('Table'[Date]) - WEEKDAY(DATE(YEAR('Table'[Date])-1,1,1))

Would likely need refinement.

[ClaireN]

Hi Greg, thanks for your reply! I added the formula (day num of year V2) and its done something very curious! am trying to pull it apart to understand why its doing what its doing, as feel like this could be a solution !

Dates

[ClaireN]

This is probably a pretty hack solution, but I just did this: 

Day Number of Year 2 = IF('Dates'[Year] = 2020, 'Dates'[Day Number of year] +1, 'Dates 2019'[Day Number of year])

I have a column in the date table for the Year and another for the day number of year... it seems like there could be a way to do it, but times a bit limited as the boss wants the report - will have to think some more about it later! 

Hope everyone out there in Power BI land is doing ok with the COVID madness - this is such a super helpful community - hang in there people!