Forum Discussion
Convert Week No to Date Range
- 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.
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
- icdns6 years agoPost Patron
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! 🙂