Forum Discussion

snamhoang's avatar
snamhoang
New Member
9 years ago
Solved

Create Week column starting from Tuesday and ending next Tuesday

Hi guys, 
I am trying to create a week column (displaying week number) which starts from Tuesday and ends next Tuesday.
Any suggestion?
I used Weeknum 21 but it starts from Mon, not Tue.

 

Thanks a lot

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    Hi snamhoang

     

    Please add this calculated column to your Calendar table

     

    Week Starting Tuesday = 
var offset = switch(FORMAT('Dates'[Date],"DDD"),
    "Tue",0,
    "Wed",-1,
    "thu",-2,
    "Fri",-3,
    "Sat",-4,
    "Sun",-5,
    "Mon",-6
    ,0)
Return DATEADD('Dates'[Date],offset,DAY)

2 Replies

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago

    Hi snamhoang,

     

    What I would do is to suggest creating a new column in the Query Editor.

     

    In doing that you can easily create it in your Date table, and from there you could then create your weeks also based off your Week Number column?

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi snamhoang

     

    Please add this calculated column to your Calendar table

     

    Week Starting Tuesday = 
var offset = switch(FORMAT('Dates'[Date],"DDD"),
    "Tue",0,
    "Wed",-1,
    "thu",-2,
    "Fri",-3,
    "Sat",-4,
    "Sun",-5,
    "Mon",-6
    ,0)
Return DATEADD('Dates'[Date],offset,DAY)