Min - Max date display based on Week number

[Anonymous]

Hi,

I need some help with combining dates for displaying in a matrix, see below blue highlights ie. "1/Feb - 3/Feb" , "4/Feb - 10/Feb", "11/Feb - 17/Feb", "18/Feb - 24/Feb", "25/Feb - 28/Feb"

Week 5

1/Feb - 3/Feb

Mon-Thu

Fri-Sat

Week 6

4/Feb - 10/Feb

Mon-Thu

Fri-Sat

Week 7

11/Feb - 17/Feb

Mon-Thu

Fri-Sat

Week 8

18/Feb - 24/Feb

Mon-Thu

Fri-Sat

Week 9

25/Feb - 28/Feb

Mon-Thu

Fri-Sat

Currently, the Date table has these columns:

Date                    Date/Month     DayRange              DayShortName 

01/02/2019     1/2                          Fri - Sun               Fri

02/02/2019     2/2                          Fri - Sun               Sat

03/02/2019     3/2                          Fri - Sun               Sun

04/02/2019     4/2                          Mon - Thu           Mon

05/02/2019     5/2                          Mon - Thu            Tue

06/02/2019     6/2                          Mon - Thu           Wed

07/02/2019     7/2                          Mon - Thu            Thu

08/02/2019     8/2                          Fri - Sun               Fri

09/02/2019     9/2                          Fri - Sun               Sat

10/02/2019     10/2                         Fri - Sun               Sun

.. and so on...

I have tried this but it returned the same min and max date.

FormatDate = CONCATENATE(CONCATENATE(CALCULATE(MIN('Date'[Date/Month]), GROUPBY('Date', 'Date'[DayRange]))   , "-") ,  CALCULATE(MAX('Date'[Date/Month]), GROUPBY('Date',  'Date'[DayRange])    ))

Any help on this would be appreciated. Thanks

[Anonymous · Accepted solution]

Hi @Anonymous ,

To achive the desired result, please go through the following steps.

Step1: Convert date into Week Start. Go to Edit Queries, and do as in the below screenshot.

Step2: Convert date into Week End. Same as above but select Week of End.

Make sure to choose the date format for the newly added columns.

Step3: Convert Week Start Date into DD/MMM. Use the below dax script.

           Week Start = FORMAT(SELECTEDVALUE(Dates[Start of Week]),"DD/MMM")

Step4: Convert Week End Date into DD/MMM. Use the below dax script.

          Week End = FORMAT(SELECTEDVALUE(Dates[End of Week]),"DD/MMM")

Step5: Combine results to get the desired output.

          Final Result = [Week Start] &" - "& [Week End]

This will give you that what you are looking for.

I have tested this and it is working fine.

Final Output will be like.

Please let me know if you have any questions. Happy to help you.

Regards,

Pavan Vanguri.