cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jwi1 Regular Visitor
Regular Visitor

Convert Year, Weeknr en Day to Date

Dear Power BI user,

 

I have the following issue.

 

I have a table containing YEAR, WEEK and NAME of the DAY MO, TUE etc).

Is it possible to convert this combination to a date (for example 02-01-2019)?

 

Thanks for your reply!

 

Knipsel.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Convert Year, Weeknr en Day to Date

Hi @jwi1 

 

You can use the below formula in calculated column.

 

StartDate = DATE([YEAR], 1, -2) - WEEKDAY(DATE([YEAR], 1, 3)) + [WEEK] * 7
 
Based on the WeekDay Name, You can create a SWITCH(TRUE()) Logic to add days to the above formula to get the exact date .
 
Date = DATE([YEAR], 1, -2) - WEEKDAY(DATE([YEAR], 1, 3)) + [WEEK] *7
+SWITCH(TRUE(),
[DAYNAME]="MON",0,
[DAYNAME]="TUE",1,
[DAYNAME]="WED",2,
[DAYNAME]="THU",3,
[DAYNAME]="FRI",4,
[DAYNAME]="SAT",5,
[DAYNAME]="SUN",6)
 
There may be other ways as well but I found this handy.
Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
2 REPLIES 2
Super User
Super User

Re: Convert Year, Weeknr en Day to Date

Hi @jwi1 

 

You can use the below formula in calculated column.

 

StartDate = DATE([YEAR], 1, -2) - WEEKDAY(DATE([YEAR], 1, 3)) + [WEEK] * 7
 
Based on the WeekDay Name, You can create a SWITCH(TRUE()) Logic to add days to the above formula to get the exact date .
 
Date = DATE([YEAR], 1, -2) - WEEKDAY(DATE([YEAR], 1, 3)) + [WEEK] *7
+SWITCH(TRUE(),
[DAYNAME]="MON",0,
[DAYNAME]="TUE",1,
[DAYNAME]="WED",2,
[DAYNAME]="THU",3,
[DAYNAME]="FRI",4,
[DAYNAME]="SAT",5,
[DAYNAME]="SUN",6)
 
There may be other ways as well but I found this handy.
Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Highlighted
jwi1 Regular Visitor
Regular Visitor

Re: Convert Year, Weeknr en Day to Date

Good day Bhavesh,

 

works perfect for me, thanks!

 

John