cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AaronRogers3 Regular Visitor
Regular Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Week commencing in DAX

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.

 

Beginning Ending of the Week.png

and this calculates the End Date of the week

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

 

Hope this helps

Regards 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

6 REPLIES 6
giblet17 Member
Member

Re: Week commencing in DAX

Hi, I don't have much time to explain but I can point you in the direction of this article:

 

https://powerpivotpro.com/2014/04/week-ending-date-calculation/

 

Hope that helps.

Super User III
Super User III

Re: Week commencing in DAX

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.

 

Beginning Ending of the Week.png

and this calculates the End Date of the week

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

 

Hope this helps

Regards 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

vichus Member
Member

Re: Week commencing in DAX

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

Super User III
Super User III

Re: Week commencing in DAX

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
KenSkinner Frequent Visitor
Frequent Visitor

Re: Week commencing in DAX

Brilliant!  Spock would be proud.  Straight forward logic and you can change the number for subtraction and addition by multiples of 7 to get the starting/ending dates for any number of weeks prior or future.  Thanks!

Highlighted
akaratr Frequent Visitor
Frequent Visitor

Re: Week commencing in DAX

Thanks. It helps.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors