Forum Discussion
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?
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?
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.
8 Replies
dpont quite undersatnd what you saying, can you put some results and expected results here?
Of 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?