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 correct start/end date with just year and weeknumber. Does anyone know how to fix this? Amit

 

Thanks in advance

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

     

5 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    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?

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

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.