Convert Week No to Date Range

[icdns]

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 

[v-alq-msft · Accepted solution]

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]

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

[amitchandak]

@icdns , depend on week start Sunday - 1 and Monday - 2 in week day

New column but it need year column too

Week to Date = var _st =Date( [Year],1,1)
var _week = right([Week],2)*1
Return _st+((_week-1)*7) -WEEKDAY(_st,2)+1

Also disucssed in my blog : https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3

[icdns]

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! 🙂