Calendar/Date help

[rp2022]

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

[Anonymous]

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 quickly

[rp2022]

Because the week is from Saturday to Friday

[rohit1991]

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))

[rp2022]

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

[rajendraongole1]

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
StartOfWeek

Hope this works. please check

[rp2022]

Thanks for the reply. But, this doesnt work