Forum Discussion
Calendar/Date help
I need help with a specific ask.
Need to calculate New Week and Reporting Date columns.
Appreciate any help
sample data:
| Date | DOW | Value | Actual Week | New Week of | Reporting date |
| 9/26/2024 | Thu | 200 | 4 | 4 | 9/26/2024 |
| 9/27/2024 | Fri | 500 | 4 | 1 | 10/1/2024 |
| 9/28/2024 | Sat | 600 | 4 | 1 | 10/1/2024 |
| 9/30/2024 | Sun | 200 | 5 | 1 | 10/1/2024 |
| 10/1/2024 | Mon | 100 | 1 | 1 | 10/1/2024 |
| 10/2/2024 | Tue | 200 | 1 | 1 | 10/1/2024 |
| 10/26/2024 | Sat | 300 | 3 | 6 | 10/26/2024 |
| 10/27/2024 | Sun | 400 | 3 | 1 | 11/1/2024 |
| 10/28/2024 | Mon | 800 | 4 | 1 | 11/1/2024 |
| 10/29/2024 | Tue | 900 | 4 | 1 | 11/1/2024 |
| 10/30/2024 | Wed | 700 | 4 | 1 | 11/1/2024 |
| 11/2/2024 | Sat | 200 | 1 | 2 | 11/2/2024 |
| 11/4/2024 | Mon | 100 | 2 | 2 | 11/4/2024 |
6 Replies
Hi rp2022 - create below calculated column for new week:
New Week =
VAR CurrentDate = 'Table'[Date]
VAR DayOfWeek = WEEKDAY(CurrentDate, 1) -- 1 = Sunday, 7 = Saturday
VAR Is27th = DAY(CurrentDate) = 27 && DayOfWeek = 6 -- 27th is a Friday
VAR Is26th = DAY(CurrentDate) = 26 && DayOfWeek = 7 -- 26th is a Saturday
VAR StartOfWeek =
IF(
Is27th,
DATE(YEAR(CurrentDate), MONTH(CurrentDate) + 1, 1),
CurrentDate - MOD(DayOfWeek + 1, 7) -- Back to Saturday
)
VAR WeekNumber =
RANKX(
FILTER('Table', YEAR([Date]) = YEAR(CurrentDate) && MONTH([Date]) = MONTH(CurrentDate)),
StartOfWeek,
, ASC
)
RETURN
IF(Is27th || Is26th, WeekNumber, WeekNumber)another columne for reporting date
Reporting Date =
VAR CurrentDate = 'Table'[Date]
VAR DayOfWeek = WEEKDAY(CurrentDate, 1) -- 1 = Sunday, 7 = Saturday
VAR StartOfWeek =
CurrentDate - MOD(DayOfWeek + 1, 7) -- Back to Saturday
RETURN
StartOfWeekHope this works. please check
Thanks for the reply. But, this doesnt work
To calculate New Week and Reporting Date in Power BI:
New Week = VAR CurrentDate = 'Table'[Date] VAR DayOfWeek = WEEKDAY(CurrentDate, 1) -- 1 = Sunday VAR Is27thFriday = (DAY(CurrentDate) = 27 && DayOfWeek = 6) VAR Is26thSaturday = (DAY(CurrentDate) = 26 && DayOfWeek = 7) RETURN SWITCH( TRUE(), Is27thFriday, 1, Is26thSaturday, MAXX(FILTER('Table', MONTH('Table'[Date]) = MONTH(CurrentDate)), 'Table'[Actual Week]) + 1, WEEKNUM(CurrentDate) - WEEKNUM(DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)) + 1 )Reporting Date (DAX):
Reporting Date = IF(WEEKDAY('Table'[Date], 1) IN {6, 7}, 'Table'[Date], DATE(YEAR('Table'[Date]), MONTH('Table'[Date]) + 1, 1))thank you for your reply. The problem with your solution is that when we have an extra week 1 into the next month (coming from Previous month for whatever reason), the actual week 1 of that month should move to week 2, and week 2 should be week 3 and so on. that doesnt happen. For eg: 9/27 is a friday, so it becomes Week 1 for month 10. then 10/4 which is actually week 1 should become week 2
Hi rp2022 ,
Thanks for reaching out.
I almost understand your requirement, but I still have doubts about your last two rows.
How does the New Week and Reporting Date calculate?
Why did the New Week change from 1 to 2 and the Reporting Date from 11/1/2024 to 11/2/2024 and 11/4/2024?
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quicklyBecause the week is from Saturday to Friday
