Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 - Worked Week Of.png

 

 

 

 

 

 

 

 

 

 

 

 

 

</>

 

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

))


</>

2 ACCEPTED SOLUTIONS
sturlaws
Resident Rockstar
Resident Rockstar

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.

View solution in original post

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 )

View solution in original post

5 REPLIES 5
sturlaws
Resident Rockstar
Resident Rockstar

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.

Simple and effective! Thanks a lot! 😎👍

Skrbass_0-1628284650602.png

Simple and effective! Thanks a lot! 😎👍

Skrbass_0-1628284481515.png

Anonymous
Not applicable

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

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 )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.