DAX Power BI 445 Calendar week Grouping

Question

Date = 
VAR MinYear = YEAR ( MIN (report1585679325399[Actual Arrive Time.1]))
VAR MaxYear = YEAR ( MAX ( report1585679325399[Actual Arrive Time.1]) )
RETURN
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO( ), 
        AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
    ),
    "Calendar Year", "CY " & YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] ),
    "Weekday", FORMAT ( [Date], "dddd" ),
    "Weekday number", WEEKDAY( [Date] ),
    "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
)

enter image description here

Above is the code I am using for my dates table. I want to add Fiscal week that starts on Saturday, i.e. week 1 of 2020 would consist Jan 1-3rd. Week 2 would be Jan 4-10. There will be 53 weeks for 2020.

I also want to add a column that says the start date and end date for each week. ie week 1, 01/01/2020, 01/04/2020.

The format would be a 445 calendar. Jan 4 weeks, Feb 4 weeks, Mar 5 weeks, Apr 4 weeks, May 4 weeks, June 5 weeks etc.

Alexis Olson Alexis Olson · Accepted Answer · 2020-04-06T22:27:37

I've never heard of a week starting on a Saturday but the WEEKNUM function can do weeks starting on Sunday or Monday and starting on Sunday lines up with your Weekday number.

To get the start date for the week starting on Saturday you can Date - Weekday number except for the first week where you'll want to take Jan. 1.

WeekStart = MAX ( [Date] - [Weekday number], STARTOFYEAR ( [Date] ) )

DAX Power BI 445 Calendar week Grouping

1 Answers