First & Last date of the week of a year

[analystict]

Hi,

I have the following table called DateTable:

Date          | Month | Year | WeekNum
6/12/2016     | 6     | 2016 | 25
6/13/2016     | 6     | 2016 | 25
6/14/2016     | 6     | 2016 | 25

Basically, what I want to do is to add another column called WeekNumRange which will be the range of dates (first & last dates) of the week of the year.

Example result:

Date          | Month | Year | WeekNum | WeekNumRange
6/12/2016     | 6     | 2016 | 25      | 6/12/2016 - 6/14/2016
6/13/2016     | 6     | 2016 | 25      | 6/12/2016 - 6/14/2016
6/14/2016     | 6     | 2016 | 25      | 6/12/2016 - 6/14/2016

How can I achieve that?

Thanks!

[analystict · Accepted solution]

I finally managed to find the right formula for the column. If anyone comes across this:

WeekNumRange = 

    VAR Range1 = 
        MINX(
            FILTER (
                ALL ( DateTable ),
                DateTable[Year] = EARLIER ( DateTable[Year] ) && DateTable[WeekNum = EARLIER ( DateTable[WeekNum] )
            ),
            DateTable[Date]
        )
    VAR Range2 =
        MAXX(
            FILTER (
                ALL ( DateTable ),
                DateTable[Year] = EARLIER ( DateTable[Year] ) && DateTable[WeekNum] = EARLIER ( DateTable[WeekNum] )
            ),
            DateTable[Date]
        )

    VAR Range = Range1 & " - " & Range2
    
    RETURN Range

[TeigeGao]

Hi @analystict ,

According to your description, my understanding is that you want to get the day range of current week in your list.

In this scenario, we can create a measure with the following DAX query:

WeekNumRange =
CONCATENATE (
    CONCATENATE (
        CALCULATE (
            MIN ( DateTable[Date] ),
            FILTER (
                ALL ( DateTable ),
                DateTable[Year] = MIN ( DateTable[Year] )
                    && DateTable[WeekNum] = MIN ( DateTable[WeekNum] )
            )
        ),
        " - "
    ),
    CALCULATE (
        MAX ( DateTable[Date] ),
        FILTER (
            ALL ( DateTable ),
            DateTable[Year] = MIN ( DateTable[Year] )
                && DateTable[WeekNum] = MIN ( DateTable[WeekNum] )
        )
    )
)

The result will like below:

Best Regards,

Teige

[analystict]

Hi @TeigeGao ,

Thank you for the reply!

It gives me the same result for the whole table (when week 2 starts, it gives the ranges of week 1).

P.S.: I use it in a form of a column.