Forum Discussion
Calculating Week number from today to back dated for last 5 Week
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
@
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#" ) ) )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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- lbendlin
Super User
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#" ) ) ) 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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Thanks a lot for helping me. it's working now
Thank you for helping me, it's working now
