DAX Date Table - (week start - week end) column

[Anonymous]

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 countless forum posts, and tried again.  I just cannot seem to get this to work.  Any help would be greatly appreciated. 

The SQL output is in the snip below (I need to replicate 3rd column).  I have also posted the SQL query and the current Date Table DAX.

Thanks!

</>

SQL Query

SELECT DISTINCT

FORMAT( tworkdt , 'MM-dd-yyyy') as WorkDate
,DATEPART(WEEK, tworkdt) as WeekNum
, CONCAT ( ( FORMAT( ( DATEADD(dd, -(DATEPART(dw, tworkdt)-1), tworkdt) ) , 'MMM-dd') ), ' - ', ( FORMAT ( ( DATEADD(dd, 7-(DATEPART(dw, tworkdt)), tworkdt) ) , 'MMM-dd') )) as WorkedWeekOf

from timecard

where tworkdt between '06/01/2020' and '06/16/20'

order by WorkDate

DAX - Date Table 

Dates =

VAR BaseCalendar =

CALENDARAUTO(12)

RETURN

GENERATE(

BaseCalendar,

VAR BaseDate = [Date]

VAR WeekNumb = WEEKNUM( BaseDate )

VAR YearDate = YEAR ( BaseDate )

VAR MonthNumber = MONTH ( BaseDate )

RETURN ROW (

"Day", BaseDate,

"Week Num", WeekNumb ,

"Week of 2" , FORMAT ( WEEKDAY( BaseDate , 1 ) , "mm dd yy" ),

"Week Of", FORMAT ( WEEKDAY( BaseDate , 1 ), "MMM dd" ) ,

"Year", YearDate ,

"Month Number", MonthNumber,

"Month", FORMAT ( BaseDate, "mmmm" ),

"MMM", FORMAT ( BaseDate , "MMMM" ),

"Year Month", FORMAT ( BaseDate, "mmm yy" ),

"Quarter", FORMAT ( BaseDate , "\Qtr Q "),

"Month Year", FORMAT ( BaseDate, "mmm yy" ),

"M# Year", FORMAT(BaseDate, "mm yy")

))

</>

[sturlaws · Accepted solution]

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.

[sturlaws · Accepted solution]

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.

[Syndicate_Admin]

Simple and effective! Thanks a lot! 😎👍

[Syndicate_Admin]

Simple and effective! Thanks a lot! 😎👍

[Anonymous]

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 · Accepted solution]

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 )