Forum Discussion

icdns's avatar
icdns
Post Patron
6 years ago
Solved

Convert Week No to Date Range

Hello,    Would like to ask for your help. I wanted to convert my Week No into Date Range, for example:  Week 46 = 11/10/2019 - 11/16/2019 (Start on a Sunday to Saturday)    I have created a dim...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, icdns 

     

    Based on your description, I created data to reproduce your scenario.

    Calendar(a calculated table):

    Calendar = CALENDAR(DATE(2019,1,1),DATE(2020,12,31))

     

    Calculated column:

    Year = YEAR('Calendar'[Date])
Week of Year = WEEKNUM('Calendar'[Date])

     

    You may create a calculated column or a measure as below.

    Calculated column:
Date Range column = 
var _mindate = 
CALCULATE(
    MIN('Calendar'[Date]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Year]=EARLIER('Calendar'[Year])&&
        'Calendar'[Week of Year]=EARLIER('Calendar'[Week of Year])
    )
)
var _maxdate = 
CALCULATE(
    MAX('Calendar'[Date]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Year]=EARLIER('Calendar'[Year])&&
        'Calendar'[Week of Year]=EARLIER('Calendar'[Week of Year])
    )
)
return
_mindate&" - "&_maxdate

Measure:
Date Range measure = 
var _mindate = 
CALCULATE(
    MIN('Calendar'[Date]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Year]=SELECTEDVALUE('Calendar'[Year])&&
        'Calendar'[Week of Year]=SELECTEDVALUE('Calendar'[Week of Year])
    )
)
var _maxdate = 
CALCULATE(
    MAX('Calendar'[Date]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Year]=SELECTEDVALUE('Calendar'[Year])&&
        'Calendar'[Week of Year]=SELECTEDVALUE('Calendar'[Week of Year])
    )
)
return
_mindate&" - "&_maxdate

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

On your Date table, you can add a YearWeek column with

YearWeek = YEAR('Date'[Date]) & WEEKNUM('Date'[Date])
 
And then add your Week Range column with this expression
 

 

Week Range =
VAR thisweekmin =
    CALCULATE ( MIN ( 'Date'[Date] ), ALLEXCEPT ( 'Date', 'Date'[YearWeek] ) )
VAR thisweekmax =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLEXCEPT ( 'Date', 'Date'[YearWeek] ) )
RETURN
    FORMAT (
        thisweekmin,
        "mm/dd/yyyy" & "-"
            & FORMAT ( thisweekmax, "mm/dd/yyyy" )
    )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat