Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 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")

))


</>

Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    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's avatar
    sturlaws
    6 years ago

    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 )

5 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        sturlaws
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago

        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 )