Forum Discussion
Create week number
Hi,
I have a data series starting on the week of 7/29/2019. I need a formula that flag with 1 to the first week (7/29/2019), 2 to the second week (8/5/2019) and so on.
How can I tell DAX to start on the week of 7/29/2019 and incrementally add 1 to the following weeks?
Thanks!
Ezequiel
Hello ezequiel
Give this a try.
Column = DATEDIFF ( DATE ( 2019 , 7 , 28 ), 'YourTable'[Date] , WEEK ) +1
The +1 is on the end so the first 7 days don't come back as 0
3 Replies
Hello ezequiel
Give this a try.
Column = DATEDIFF ( DATE ( 2019 , 7 , 28 ), 'YourTable'[Date] , WEEK ) +1
The +1 is on the end so the first 7 days don't come back as 0
Hi jdbuchanan71 . Thanks for your reply.
I tried with
WeekNum = DATEDIFF (DATE(2019,7,28),Tracker[Start of Week],WEEK) +1
I forgot to mention that the table contains this structure:
Subject Hours Start of Week
A 2 7/29/2019
B 3 7/29/2019
A 2 7/29/2019
B 3 8/5/2019
A 3 8/5/2019
The intention is getting something like:
Subject Hours Start of Week Weeknum
A 2 7/29/2019 1
B 3 7/29/2019 1
A 2 7/29/2019 1
B 3 8/5/2019 2
A 3 8/5/2019 2
The suggestion you sent me is giving me an error indicating that "A single value for the column 'Start of Week' in table 'Tracker' cannot be determined"
Thanks
Ezequiel
You would have to add this as a calculated column in your table, not a measure.
