cancel
Showing results for
Did you mean:
Highlighted
Helper I

## 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
Highlighted
Super User IV

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

and this calculates the End Date of the week

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

Hope this helps

Regards

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
6 REPLIES 6
Highlighted
Resolver II

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

Highlighted
Super User IV

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

and this calculates the End Date of the week

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

Hope this helps

Regards

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
Helper IV

## Re: Week commencing in DAX

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

Highlighted
Super User IV

## Re: Week commencing in DAX

Hey,

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Highlighted
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
Frequent Visitor

## Re: Week commencing in DAX

Thanks. It helps.

Announcements

#### Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

#### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors