DAX - Week End Date

[Anonymous]

Hi All,

I am creating a date table and I would like a column for the end of week (sunday) for each week. I've tried;

"Week End Date", [Date] - WEEKDAY([Date],2)+7,

But you can see it isn't working.

"Week End Date", [Date] - MOD([Date]-2,7)+6,

This doesn't work either.

Date =
VAR MinYear = YEAR ( MIN ( 'Flights'[Start date ] ) )
VAR MaxYear = YEAR ( MAX ( 'Flights'[Start date ] ) )

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] ),
"Week End Date", [Date] - WEEKDAY([Date],2)+7,
"Week Number", WEEKNUM([Date],2),
"Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
)

Thanks for any help.

[Shadow_BN515]

Thank you for this, I search everywhere. This is so simple and works great!

[amitchandak · Accepted solution]

@Anonymous , weekend

Sunday

Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

Saturday

Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],1)

for all other week day, refer to my blog and files

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482