cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Need day number to be different for different years

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

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Need day number to be different for different years

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Need day number to be different for different years

 

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 !

DatesDates

 

Highlighted
Frequent Visitor

Re: Need day number to be different for different years

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! 

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021