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 dimension table for dates, an I am using Week of Year for my "Week No: 

 

I have tried creating some columns but it's not working 😞

 

Can someone help me out? Thank you so much!

 

- IC 

 

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.

     

4 Replies

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

     

    • icdns's avatar
      icdns
      Post Patron
      6 years ago

      Hi!

       

      Thank you for your prompt feedback! However, how can I convert it into: 

       

      11/10/2019 - 10/16/2019 format? instead of just one date. 

       

      Thanks in advance! 🙂 

  • 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