Calculating Week number from today to back dated for last 5 Week

[SurendraD]

Hi All, 

I have a column of dates like this 

Date

3/22/2024

3/21/2024

3/20/2024

3/19/2024

3/18/2024

3/17/2024

3/16/2024

3/15/2024

3/14/2024

3/13/2024

3/12/2024

3/11/2024

3/10/2024

3/9/2024

3/8/2024

3/7/2024

3/6/2024

3/5/2024

3/4/2024

3/3/2024

3/2/2024

3/1/2024

2/29/2024

2/28/2024

2/27/2024

2/26/2024

2/25/2024

2/24/2024

2/23/2024

2/22/2024

2/21/2024

2/20/2024

2/19/2024

I want to calculate the week numbers from today to backward till completed the 5 Weeks and by starts with monday.


Result will be like this 

Date	Week
3/22/2024	W1
3/21/2024	W1
3/20/2024	W1
3/19/2024	W1
3/18/2024	W1
3/17/2024	W2
3/16/2024	W2
3/15/2024	W2
3/14/2024	W2
3/13/2024	W2
3/12/2024	W2
3/11/2024	W2
3/10/2024	W3
3/9/2024	W3
3/8/2024	W3
3/7/2024	W3
3/6/2024	W3
3/5/2024	W3
3/4/2024	W3
3/3/2024	W4
3/2/2024	W4
3/1/2024	W4
2/29/2024	W4
2/28/2024	W4
2/27/2024	W4
2/26/2024	W4
2/25/2024	W5
2/24/2024	W5
2/23/2024	W5
2/22/2024	W5
2/21/2024	W5
2/20/2024	W5
2/19/2024	W5

Can any one please help me to fix this 
@dax @dax @daxdax  @power @Anonymous 
@

[lbendlin · Accepted solution]

Dates =
ADDCOLUMNS (
    CALENDAR ( "2024-01-01", TODAY () ),
    "WeekBack",
        VAR wd =
            WEEKDAY ( TODAY (), 2 )
        VAR wb =
            INT ( DIVIDE ( 14 + TODAY () - wd - [Date], 7 ) )
        RETURN
            IF ( wb < 6, FORMAT ( wb, "\W#" ) )
)

[SurendraD]

Thank you for helping me, it's working now 

[Anonymous · Accepted solution]

Hi @SurendraD 

Your solution is great, @lbendlin. It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.

You can try this calculated column as follows.

Week Number = 
VAR CurrentWeek = WEEKNUM(MAX([Date]), 2)
VAR DateWeek = WEEKNUM([Date], 2)
RETURN IF((CurrentWeek - DateWeek + 1) <= 5, "W" & CurrentWeek - DateWeek + 1, BLANK())

Result:

When I changed the date to today (3/25/2024):

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SurendraD]

Thanks a lot for helping me. it's working now

[lbendlin · Accepted solution]

Dates =
ADDCOLUMNS (
    CALENDAR ( "2024-01-01", TODAY () ),
    "WeekBack",
        VAR wd =
            WEEKDAY ( TODAY (), 2 )
        VAR wb =
            INT ( DIVIDE ( 14 + TODAY () - wd - [Date], 7 ) )
        RETURN
            IF ( wb < 6, FORMAT ( wb, "\W#" ) )
)