CODEWITHSUNDEEP
powerbidax
Nina
Nina

Reputation: 21

DAX Power BI 445 Calendar week Grouping

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.

Upvotes: 1

Views: 787

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

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

Upvotes: 0

Related Questions