Forum Discussion
DAX Date Table - (week start - week end) column
I need to add another column to my DAX date table. The cloumn needs to have "Week Start Date - Week End Date" and I would like for each date to be formatted as mmm dd. I have tried, read countle...
Hi Anonymous
try this:
Column = FORMAT ( 'Dim dates'[Date] - WEEKDAY ( 'Dim dates'[Date], 1 ) + 1, "MMM-dd" ) & " - " & FORMAT ( 'Dim dates'[Date] + 7 - WEEKDAY ( 'Dim dates'[Date], 1 ), "MMM-dd" )Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.or do something like this
weekSort = YEAR ( 'Dim dates'[Date] - WEEKDAY ( 'Dim dates'[Date], 1 ) + 1 ) * 100 + WEEKNUM ( 'Dim dates'[Date] - WEEKDAY ( 'Dim dates'[Date], 1 ) + 1 )
sturlaws
Resident Rockstar
Resident RockstarHi Anonymous
try this:
Column =
FORMAT ( 'Dim dates'[Date] - WEEKDAY ( 'Dim dates'[Date], 1 ) + 1, "MMM-dd" ) & " - "
& FORMAT ( 'Dim dates'[Date] + 7 - WEEKDAY ( 'Dim dates'[Date], 1 ), "MMM-dd" )
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
TY Sturla! This works great and I will mark as a solution shortly.
Any ideas on a sort column? It is currently sorting alphabetically.
NVM I modified your line to inlcude yy and used that to sort! TYVM
"Week of Sort", FORMAT ( BaseDate - WEEKDAY ( BaseDate, 1 ) + 1, "yy MM dd " ) & " - " & FORMAT ( BaseDate + 7 - WEEKDAY ( BaseDate, 1 ), "yy MM dd" ),
"Week Of" , FORMAT ( BaseDate - WEEKDAY ( BaseDate, 1 ) + 1, "MMM dd" ) & " - " & FORMAT ( BaseDate + 7 - WEEKDAY ( BaseDate, 1 ), "MMM dd" ),
- sturlaws
or do something like this
weekSort = YEAR ( 'Dim dates'[Date] - WEEKDAY ( 'Dim dates'[Date], 1 ) + 1 ) * 100 + WEEKNUM ( 'Dim dates'[Date] - WEEKDAY ( 'Dim dates'[Date], 1 ) + 1 )