Forum Discussion
DAX - Week End Date
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.
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.
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
2 Replies
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/1187482Thank you for this, I search everywhere. This is so simple and works great!
