Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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!

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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!