cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
12scml
Resolver I
Resolver I

Weeknum starting on Friday

Hi! I am trying to create a new column with the WEEKNUM function, however I need the weeks to start on Friday, not on Sunday or Monday. 

Right now, I have a column titled Week Number that was made with WEEKNUM = WEEKNUM([Dates],1). To change the start date back, I have tried using
WEEKNUM(DATEADD([Dates],-2,DAY),1).

The issue was that this column couldn't be made because my Dates column has repeated values. Due to the nature of my data, it's impossible to remove the duplicate dates 😞 

Is there any way that I can get around this?

3 ACCEPTED SOLUTIONS

The DATEADD([Dates],-2,DAY) part of the equation doesn't work because some of the dates in my [Dates] column are repeating.

View solution in original post

then create a new column with date only and work out the week num with your calculation?

 

what error does it give?  i still dont see how that is an issue, why does it care if there is more than one date the same?  maybe thats me just with my sql mind on, why not format it as a date only for the calculation and leave the time bit off?  





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

No, I don't have to! I've been working with them both though and both DATEADD and WEEKNUM work just fine with the times. I was able to solve the issue though by (I don't understand how this works) duplicating the column and then using the DAX function.

View solution in original post

8 REPLIES 8
vanessafvg
Super User
Super User

dpont quite undersatnd what you saying, can you put some results and expected results here?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




PowerBiForumStartDate.PNGOf course! Here's a picture of what my results are. It's making week 1 start on Thursday, January 1, 2015 and end on Saturday the 3rd. I am trying to have week 1 start on Saturday, January 3rd, 2015 and go until Friday, January 9, 2015. 

I looked into how the WEEKNUM function works, and it always makes week 1 the first week with a thursday in it, then either lets the week start on Sundays or Mondays. I want to make it so that week 1 is the first full week of the year, starting on Saturdays. 

so what is the problem with the duplicate date i dont understand?

 

why dont you split your date and time in powerquery, do the WEEKNUM(DATEADD([Dates],-2,DAY),1) (or similar)  and then remerge it? 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




The DATEADD([Dates],-2,DAY) part of the equation doesn't work because some of the dates in my [Dates] column are repeating.

then create a new column with date only and work out the week num with your calculation?

 

what error does it give?  i still dont see how that is an issue, why does it care if there is more than one date the same?  maybe thats me just with my sql mind on, why not format it as a date only for the calculation and leave the time bit off?  





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




I'm not sure what happened, I didn't mean to accept the solution but am unsure of how to revoke it. When I try my funtion, I get this error: 
A date column containing duplicate dates was specified in the call to function 'DATEADD'. This is not supported.

I created a new column and removed the duplicates, however this leaves all of the removed data points with no week number.

do you have to keep your date and time together?  i did some googling and yes you are right, it doesn't like duplicate values 

 

http://stackoverflow.com/questions/39679183/issue-with-dateadd-function-in-dax





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




No, I don't have to! I've been working with them both though and both DATEADD and WEEKNUM work just fine with the times. I was able to solve the issue though by (I don't understand how this works) duplicating the column and then using the DAX function.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.