cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User III
Super User III

@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
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors