Calculate the Sunday Date

[jdw_rhodes]

Hello,

I'm trying to take a date, let's say 11/14/2023, and return the Sunday of the week that day falls within. For my PowerBI report, Monday is the first day of the week. So for 11/14/2023, the Sunday of that week it falls within would be 11/19/2023. I've looked at some date functions for dax but can't quite find what I'm looking for. Thank you for your help!

[jdw_rhodes]

Yes that's what I was thinking. Making a column of Sunday of Week that is. Thank you so much!

[OwenAuger · Accepted solution]

Hi @jdw_rhodes 

The basic calculation you could use to convert a date to the Sunday of the same Mon-Sun week would be:

<Date> + 7 - WEEKDAY ( <Date>, 2 )

where <Date> is any expression returning a date.

The exact way to apply this would depend on the context.

For example, you could add a "Sunday of Week"column to your Date dimension table (using the above logic), and reference this column as required. This might be more efficient than computing the Sunday date on the fly.

Regards