topic Re: Calculate the Sunday Date in DAX Commands and Tips

Calculate the Sunday Date

jdw_rhodes — Wed, 15 Nov 2023 19:06:28 GMT

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!

Re: Calculate the Sunday Date

OwenAuger — Wed, 15 Nov 2023 19:58:11 GMT

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

Re: Calculate the Sunday Date

jdw_rhodes — Thu, 16 Nov 2023 19:13:16 GMT

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