Forum Discussion
Solved
Need help converting YEAR/WEEK_NUMBER (text format) into date format
I have data that has year and week number like 2020/02. Power BI detects it as text but not as date. How could I convert this into another column and so that it could detect it as a week number? ...
Thank you all for your help. I'm quite new to Power BI and I think I wasn't able to describe my problem properly and that's my fault. I got a link from a friend of mine to this blog post that helped in my problem: https://eriksvensen.wordpress.com/2019/11/26/powerquery-calculate-the-iso-date-from-year-and-a-week-number/
ImkeF can probably help with a Power Query solution.
In DAX it would be below. What day of the week do your weeks start on?
Column =
VAR __Year = VALUE(LEFT([YearWeek],4))
VAR __Week = VALUE(RIGHT([YearWeek],2)) //Using VALUE only for its intended purpose of converting a text value to a number
VAR __Calendar = ADDCOLUMNS(CALENDAR(DATE(__Year,1,1),DATE(__Year,12,31)),"Week",WEEKNUM([Date]))
RETURN
MINX(FILTER(__Calendar,[Week]=__Week),[Date])
Hi Greg,
I've been searching for this for quite some time. Used your formula but with Weeknum([Date],21) and got isoWeek.Thanks a lot!
Thanks Greg for your answer, I'll wait also for the power query solution since that would be ideal. My weeks start on Monday.
