Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PowerMarra
Regular Visitor

Connect week_year with calendar table

Hi,

 

I have a problem where I have data with year and weeknumber that I need to connect to a calendar table. This must be done with a date key in the fact table, but I am not sure how to produce a correct start/end date with just year and weeknumber. Does anyone know how to fix this? @Amit

 

Thanks in advance

1 ACCEPTED SOLUTION
mahoneypat
Employee
Employee

Assuming your Year and Week columns are Integer type, you can use this expression in a custom column.  You could then make a relationship from a date table to this new column.

 

= Date.EndOfWeek(Date.AddDays(#date([Year],1,1), ([Week]-1)*7))

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
v-xiaoyan-msft
Community Support
Community Support

Hi @PowerMarra ,

 

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thank you very much for your kind cooperation!

 

Hope it helps,


Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

mahoneypat
Employee
Employee

Assuming your Year and Week columns are Integer type, you can use this expression in a custom column.  You could then make a relationship from a date table to this new column.

 

= Date.EndOfWeek(Date.AddDays(#date([Year],1,1), ([Week]-1)*7))

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Well @mahoneypat 

I have used your formula but (it gives me wrong EndOFWeek Number)
I have a week number as (Week 53 2020) 
which is Starting on Dec 28, 2020 and Ending on Jan. 3, 2021

How  to relate Calendar table and my Weekly fact table?

Date.StartOfWeek(Date.AddDays(#date([Yr],1,1), ([Wk]-1)*7))

is more robust, say, 2000/12/31 falls in the 54th week of the year.


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

@CNENFRNL 

I have only 1 week line for Week 53 2020, so I want its Start date (as Dec 28 2020) but I its showing 27
any help?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.