Forum Discussion
icdns
6 years agoPost Patron
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...
- 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&" - "&_maxdateResult:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-alq-msft
6 years agoCommunity Support
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.