Create date range column for weeks in DAX

[Anonymous]

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]

Hello, this is very useful, but what if you year does not begin with Monday ? For instance 2023 starts on a Sunday. Thus, with this formula we have Week 1 = 12/26/22 - 1/1/2023 and 1/2/2023 - 1/8/2023. How could we manage this case please?

[v-yuta-msft · Accepted solution]

@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.

[Anonymous]

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!

[CreedRG]

This was a great thread and helped me preparing a report I was looking forward to, but I just wanted to know if there is a way we can only keep the date range from Monday till Friday, please suggest. Thank you!