Convert date into last Sunday of the month (Power Query)

[Sander1401]

Hi All,

I have a facttable with data on every Sunday (e.g. 5th of december, 12th of december, etc.). I also have a budgettable with data on every first date of the month (1st of december, 1st of january, etc.).
I need to combine these 2 tables, so at first I converted the budget-date into the week's end (so Sunday), but now I need to convert it to the last Sunday of the month, so:
01-12-2021 >> 26-12-2021

Do you have any ideas how to do that in Power Query?

Thanks in advance!

[magglobal]

Hi @parry2k ,

How are you?

Can you help to achive following case?

If date anything between

1) 26-Dec-2022 to 29-Jan-2023 then I want column with date 29-Jan-2023

2) 30-Jan-2023 to 26-Feb-2023 then I want column with date 26-Feb-2023

3) 27-Feb-2023 to 26-Mar-2023 then I want column with date 26-Mar-2023

Basically, my month end when last sunday of each month, so I need to populate last sunday of the month as end date.

Can you suggest, how to achive this?

Thanks

MG

[parry2k]

@Sander1401 sorry that was typo, yes indeed it needs -1, glad it worked out. cheers!!

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@Sander1401 add a new custom column to get last Sunday of the month

Date.AddDays(Date.EndOfMonth([Column1]),-Date.DayOfWeek(Date.EndOfMonth([Column1]))+1)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Gordon_P1234]

When the last day of the month is a Sunday, this formula returns the previous weeks Sunday (24th March 2024 not the 31st March 2024 as expected). Is there any way to get around this? 

Thanks, Gordon

[Ashish_Mathur]

Hi,

This calculated column formula works as well

Column = Data[Date]-SWITCH(WEEKDAY(EOMONTH(Data[Date],0)+1,2),1-1,2-2,3,-3,4,-4,5,-5,6,-6,7,0)

Hope this helps.

[INFOTIP]

exclude when Date.DayOfWeek(Date.EndOfMonth([xxxxx]) = 6 (the last day of the month is a Sunday - 6)

I mean something like:

if Date.DayOfWeek(Date.EndOfMonth([Meter_time])) < 6

then

      Date.AddDays(Date.EndOfMonth([xxxxx]),-Date.DayOfWeek(Date.EndOfMonth([xxxxx]))-1)

else

      Date.EndOfMonth([xxxxx])

[Sander1401]

I've copied thwe formula and I got the tuesdays, so I changed the +1 into -1 and it worked. 
Great help, thank you!