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 ...
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 

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
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