Forum Discussion

rp2022's avatar
rp2022
Helper II
1 year ago

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/...
Need Help
rohit1991's avatar
rohit1991
Super User
1 year ago

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's avatar
    rp2022
    Helper II
    1 year ago

    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