cancel
Showing results for
Did you mean:
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)?

1 ACCEPTED SOLUTION

Accepted Solutions
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.
2 REPLIES 2
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.
Highlighted
Regular Visitor

## Re: Convert Year, Weeknr en Day to Date

Good day Bhavesh,

works perfect for me, thanks!

John