Forum Discussion

AaronRogers3's avatar
AaronRogers3
Helper I
8 years ago
Solved

Week commencing in DAX

Hi

 

Does anyone know the DAX for displaying the Week Commencing date?

 

I am working on a service desk based on tickets and we have a column for the date received of the ticket.

 

Now i would like to display the date of the week commencing in a new column, based on that date received column.

 

Thanks.

Need Help
Show and Tell
  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

     

    just try this "simple" DAX statement

    SoWDate = 'Calendar'[Date]  - WEEKDAY('Calendar'[Date],2) +1

    The second parameter of the WEEKDAY()-function indicates if Sunday or Monday is your first day of the week, for me this works like a charm, maybe you have to use a different correction part.

     

    Just using your Date column and the "Day of Week" column helps to adjust the above mentioned formula if necessary.

     



    and this calculates the End Date of the week

    EoWDate = 'Calendar'[Date] + 7 - WEEKDAY([DATE],2)

     

    Hope this helps

    Regards 

10 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    Hey,

     

    just try this "simple" DAX statement

    SoWDate = 'Calendar'[Date]  - WEEKDAY('Calendar'[Date],2) +1

    The second parameter of the WEEKDAY()-function indicates if Sunday or Monday is your first day of the week, for me this works like a charm, maybe you have to use a different correction part.

     

    Just using your Date column and the "Day of Week" column helps to adjust the above mentioned formula if necessary.

     



    and this calculates the End Date of the week

    EoWDate = 'Calendar'[Date] + 7 - WEEKDAY([DATE],2)

     

    Hope this helps

    Regards 

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      This is great. Could someone please explain the logic behind this.

      • TomMartens's avatar
        TomMartens
        Super User
        8 years ago

        Hey,

         

        thanks for your kind words!

         

        The thinking (not sure if Mr Spock would call this logic) behind this is as follows:

        • A week spans 7 days
        • Each day belongs to a single week
        • If my week starts on Monday Weekday("2018-07-04",2) returns 3, a Wednesday is the 3rd day of a week
        • The date of the starting week is calculating like this: Subtracting 3 Adding 1 from "2018-07-04" returns the date of the Monday closest to the date in question "2018-07-04"
        • A similar logic is used to calculate the date of the next Sunday

         

        Hopefully this explains the reasoning behind both DAX statements a little better.

         

        Regards

        Tom

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Well, only if you accept that week 1 can start in the year before this, in quite a lot of countries week 1 is the week 1ith 1 January in it and can be 1-7 days. That complicates matters

    • kdizzle's avatar
      kdizzle
      Regular Visitor
      4 years ago

      Dates are not stored that way in DAX.
      This may work in Excel but I don't think it's ok in PowerBI.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Week commencing cannot be sorted in chronological order

    Please help.

    • TomMartens's avatar
      TomMartens
      Super User
      5 years ago

      Hey Anonymous ,

       

      please consider to start a new thread, don't forget being more specific about your issue.

      If possible provide a pbix that contains sample data, upload the pbix to onedrive or dropbox and share the link. If you useExcel to create the sample data, upload the xlsx as well.

       

      Regards,

      Tom