Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
</>
Solved! Go to 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.
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 )
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! 😎👍
Simple and effective! Thanks a lot! 😎👍
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
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 )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |