cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
siros92
Frequent Visitor

week + year in seperate columns to date

Hi all! 

 

My dataset, which cannot be changed has two seperate columns to describe date; one [week] and one [year]. As the dataset spans over several years this naturally causes quite a bit of issues when trying to filter the data.

Is there any way to use power query to make the two columns into a new one which power bi will recognize as a validdate? 

Making date into the first day of week would be perfectly acceptable. 

 

Br

Frivik

 

 

1 ACCEPTED SOLUTION
avatorl
Skilled Sharer
Skilled Sharer

Add custom column Date with formula 

=Date.StartOfWeek(Date.AddDays(#date([year],1,1),([week]-1)*7),Day.Monday)



#date([year],1,1) returns January 1st of the [year],
then you add 7 days for each week using Date.AddDays
then you get first day of week using Date.StartOfWeek

View solution in original post

2 REPLIES 2
avatorl
Skilled Sharer
Skilled Sharer

Add custom column Date with formula 

=Date.StartOfWeek(Date.AddDays(#date([year],1,1),([week]-1)*7),Day.Monday)



#date([year],1,1) returns January 1st of the [year],
then you add 7 days for each week using Date.AddDays
then you get first day of week using Date.StartOfWeek

View solution in original post

siros92
Frequent Visitor

And you sir are a genious. Thanks for helping out a newbie! 

Enjoy your weekend. 

Best regards

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors