cancel
Showing results for
Did you mean:
Highlighted
Helper I

## 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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Create date range column for weeks in DAX

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.

2 REPLIES 2
Highlighted
Community Support

## Re: Create date range column for weeks in DAX

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.

Highlighted
Helper I

## Re: Create date range column for weeks in DAX

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!

Announcements

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors
Top Kudoed Authors