Forum Discussion

ezequiel's avatar
ezequiel
Microsoft Employee
6 years ago
Solved

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 s...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    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's avatar
ezequiel
Microsoft Employee
6 years ago

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's avatar
jdbuchanan71
Super User
6 years ago

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