Forum Discussion
Create date range column for weeks in DAX
So I have a list of dates (from Monday, December 10, 2018 onwards) in a table. It's a table originally created in the Data View, so is not a query and cannot be edited in the Query Editor.
I want to assign each of these dates to calendar weeks starting on Monday, and have a calculated column that displays the first date in the week and the last date in the week, e.g.:
| Monday, December 10, 2018 | 10/12/2018-16/12/2018 |
| Tuesday, December 11, 2018 | 10/12/2018-16/12/2018 |
| Wednesday. December 12, 2018 | 10/12/2018-16/12/2018 |
| Thursday, December 13, 2018 | 10/12/2018-16/12/2018 |
| Friday, December 14, 2018 | 10/12/2018-16/12/2018 |
| Saturday, December 15, 2018 | 10/12/2018-16/12/2018 |
| Sunday, December 16, 2018 | 10/12/2018-16/12/2018 |
| Monday, December 17, 2018 | 17/12/2018-23/12/2018 |
| Tuesday, December 18, 2018 | 17/12/2018-23/12/2018 |
Any ideas on how I could write a formula to achieve this?
Anonymous,
You may try DAX like pattern below(maybe some mistakes but hope it can guide you):
Result = VAR seperator = FIND ( "-", SUBSTITUTE ( Table1[Date], "-", "," ) ) VAR weekday = LEFT ( Table1[Date], seperator - 1 ) VAR _date = RIGHT ( Table1[Date], LEN ( Table1[Date] ) - seperator ) RETURN SWITCH ( weekday, "Monday", CONCATENATE ( _date, CONCATENATE ( "~", _date + 6 ) ), "Tuesday", CONCATENATE ( _date - 1, CONCATENATE ( "~", _date + 5 ) ), "Wednesday", CONCATENATE ( _date - 2, CONCATENATE ( "~", _date + 4 ) ), "Thursday", CONCATENATE ( _date - 3, CONCATENATE ( "~", _date + 3 ) ), "Friday", CONCATENATE ( _date - 4, CONCATENATE ( "~", _date + 2 ) ), "Saturday", CONCATENATE ( _date - 5, CONCATENATE ( "~", _date + 1 ) ), "Sunday", CONCATENATE ( _date - 6, CONCATENATE ( "~", _date ) ) )Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous,
You may try DAX like pattern below(maybe some mistakes but hope it can guide you):
Result = VAR seperator = FIND ( "-", SUBSTITUTE ( Table1[Date], "-", "," ) ) VAR weekday = LEFT ( Table1[Date], seperator - 1 ) VAR _date = RIGHT ( Table1[Date], LEN ( Table1[Date] ) - seperator ) RETURN SWITCH ( weekday, "Monday", CONCATENATE ( _date, CONCATENATE ( "~", _date + 6 ) ), "Tuesday", CONCATENATE ( _date - 1, CONCATENATE ( "~", _date + 5 ) ), "Wednesday", CONCATENATE ( _date - 2, CONCATENATE ( "~", _date + 4 ) ), "Thursday", CONCATENATE ( _date - 3, CONCATENATE ( "~", _date + 3 ) ), "Friday", CONCATENATE ( _date - 4, CONCATENATE ( "~", _date + 2 ) ), "Saturday", CONCATENATE ( _date - 5, CONCATENATE ( "~", _date + 1 ) ), "Sunday", CONCATENATE ( _date - 6, CONCATENATE ( "~", _date ) ) )Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Jimmy,
This worked for me great, slightly tweaked as follows:
Calendar Week Date Range = VAR separator = FIND("-",SUBSTITUTE(FORMAT('6 Date Table'[Date],"Long Date"),", ","-")) VAR weekday = LEFT(FORMAT('6 Date Table'[Date],"Long Date"), separator - 1) VAR _date = RIGHT(FORMAT('6 Date Table'[Date],"Long Date"),LEN(FORMAT('6 Date Table'[Date],"Long Date")) - separator) RETURN SWITCH ( weekday, "Monday",CONCATENATE(FORMAT(_date + 0,"dd/mm/yyyy"),CONCATENATE("-",FORMAT(_date + 6,"dd/mm/yyyy"))), "Tuesday",CONCATENATE(FORMAT( _date - 1,"dd/mm/yyyy"),CONCATENATE("-",FORMAT(_date + 5,"dd/mm/yyyy"))), "Wednesday",CONCATENATE(FORMAT(_date - 2,"dd/mm/yyyy"),CONCATENATE("-",FORMAT(_date + 4,"dd/mm/yyyy"))), "Thursday",CONCATENATE(FORMAT(_date - 3,"dd/mm/yyyy"),CONCATENATE("-",FORMAT(_date + 3,"dd/mm/yyyy"))), "Friday",CONCATENATE(FORMAT(_date - 4,"dd/mm/yyyy"),CONCATENATE("-",FORMAT(_date + 2,"dd/mm/yyyy"))), "Saturday",CONCATENATE(FORMAT(_date - 5,"dd/mm/yyyy"),CONCATENATE("-",FORMAT(_date + 1,"dd/mm/yyyy"))), "Sunday",CONCATENATE(FORMAT(_date - 6,"dd/mm/yyyy"),CONCATENATE("-",FORMAT(_date + 0,"dd/mm/yyyy"))) )This was a great learning exercise for me! Thanks so much for your help!