Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
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
To learn more about Power BI, follow me on Twitter or subscribe 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! |
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?
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |