cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
irnm8dn
Post Prodigy
Post Prodigy

Including Week Ending Column in Date Table- DAX Help!!!

I am currently using the following DAX statement to create a date table...

 

Date =

ADDCOLUMNS (

CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthShortYear",FORMAT([Date], "mmm-YYYY"),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),

"Week Number", WEEKNUM ( [Date] ),

"Week Number and Year", "W" & WEEKNUM ( [Date] ) & " " & YEAR ( [Date] ),

"WeekYearNumber", YEAR ( [Date] ) & 100 + WEEKNUM ( [Date] )

)

 

I'd like to add a column that shows a "week ending" date formatted as mm/dd/yyyy so that I can aggreagte information by week ending date.  Typical week Sunday thru Saturday.

 

**Also, if someone has a more useful direction to create a DateDim table, I am all ears.

 

Appreciate all of the help.

1 ACCEPTED SOLUTION

@parry2k 

 

Found this elsewhere - seems to do the trick.  Any thoughts?

 

WeekEnding = 'Date'[Date]-MOD('Date'[Date]-1,7)+6

View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

@irnm8dn check this link, in comment there are many calculation to get weekend date.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k 

 

Found this elsewhere - seems to do the trick.  Any thoughts?

 

WeekEnding = 'Date'[Date]-MOD('Date'[Date]-1,7)+6

View solution in original post

@irnm8dn looks good. 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors