Forum Discussion

cheid_4838's avatar
cheid_4838
Icon for Helper IV rankHelper IV
2 years ago

Week Start Dating Not Calculating Correctly

I am having issues calculating week start date and having two dates appear.  One is correct and the second is always the week prior as you will see in the screenshot.  I have a date table linked to the delivery date in the invoice header table.  WIth this relationship built I calcualted the week start date with the following formula: 

WEEK START DATE = 'delivery date'[Date]-weekday('delivery date'[Date],2)

 

 

I am not sure what could be driving this.  Is there something in the relationship that could be driving two two dates?  Thanks for your help.

Need Help

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    cheid_4838 I don't understand what I am seeing exactly in the screen shot. Is your 11/12 correct and 11/19 incorrect or the other way around?

    • cheid_4838's avatar
      cheid_4838
      Icon for Helper IV rankHelper IV
      2 years ago

      11/19 is correct, but 11/12 is showing up.  That was an important piece of information if left out. 🙂

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        2 years ago

        cheid_4838 OK, I *think* the issue is that the way you have your formula written, 1/19/2023 will have a start date of 1/12/2023 (1/19/2023 - 7 = 1/12/2023). I think that has something to do with it but very difficult to tell as I don't really have much context as to what is what in that visual and where things are coming from.

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    2 years ago

    All reports use dates, so it is very important that power Bi developers learn how to use Calandar tables.

     

    Try this ....

    Click here to download PBIX from OneDrive 

     

    How it works ...
    Create a calendar table

    Calendar =  CALENDAR(DATE(2023,01,01),DATE(2024,12,31))

     

    Add a start of week column (I have used Monday)

    Start of week = 'Calendar'[Date] - WEEKDAY('Calendar'[Date],2) + 1

     

    Add a week offset column

    Week offset = DATEDIFF(TODAY() - WEEKDAY(TODAY(),2) + 1,[Start of week],DAY) / 7

     

    This will refresh each time your data is refeshed to give you the week offset 


    Create Calandar date to Fact date one to many relationship

     

    Build your report and add a filter to just shoiw the week offset you need.

    This example just shows the last 5 weeks, but you can choose just one week if you wish

     


    To learn more about Calendars watch these videos  
    https://www.youtube.com/watch?v=BtYn1hfdSAM 

     

    Thanks for the clear description of the problem with example data. I wish everyone did that!

    Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
    This solution works and does exactly what you asked.
    So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

    One question per ticket please. If you need to change or extend your request then please raise a new ticket.

    You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

    If you quote speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

    Please now click the [accept as solution] and the thumbs up button.  Thank you.