Create week number

[ezequiel]

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

[jdbuchanan71 · Accepted solution]

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

[ezequiel]

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

[jdbuchanan71]

You would have to add this as a calculated column in your table, not a measure.