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...
  • v-alq-msft's avatar
    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.