Forum Discussion

PowerMarra's avatar
PowerMarra
Regular Visitor
5 years ago
Solved

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...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

     

CNENFRNL's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
5 years ago
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.

MH3's avatar
MH3
Icon for Helper V rankHelper V
4 years ago

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?