How to calculate the last sunday of month March and Last sunday of Month October

[AyubKhan]

Hope you all doing well. I am new to Alteryx designer.

I am stuck while designing the workflow where the requirement is I want to calculate the date of last Sunday of the month of March and October irrespective of the year. 

For Example, For the year 2020

March: 29-03-2020 (Sunday)

October: 25-10-2020 (Sunday)

For Example, For the Year 2021

March: 28-03-2020 (Sunday)

October: 31-10-2020 (Sunday)

So you can see data is changing, I want to calculate the date dynamically.

Thanks,

Ayub Khan

[tsturge]

Hopefully this gives you an idea and starting point, I am sure there are other ways but this is what we do without using a 2nd helper table.

[atcodedog05 · Accepted solution]

Hi @AyubKhan 

Here is a formula for the task. I am just taking the weekday of first of next month and adjusting the dates

DateTimeAdd(
DateTimeAdd(DateTimeParse(ToString([Year])+ToString([Month]),"%y%m"),1,"month"),
-iif(ToNumber(
DateTimeFormat(
DateTimeAdd(DateTimeParse(ToString([Year])+ToString([Month]),"%y%m"),1,"month")
,"%w"))=0,7,
-ToNumber(
DateTimeFormat(
DateTimeAdd(DateTimeParse(ToString([Year])+ToString([Month]),"%y%m"),1,"month")
,"%w"))),
"days")

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Qiu]

@AyubKhan 
How about a more straightforward way/

[AyubKhan]

Hi @atcodedog5

Thanks for the response.

One question here if I want to run this workflow dynamically. For example

when it runs for 2020, it should only give me the output date for the last Sunday of March and the Last Sunday of October for 2020

when 2021 starts the from 1st fo Jan it should give me the output date for the last Sunday of March and Last Sunday of October for 2021.

I do not want to hard code the input values.

Thanks,

[atcodedog05]

Hi @AyubKhan 

Sure thats possible. You can give the month and get year from todays date

Here is formula to get the current year.

DateTimeYear(DateTimeToday())

I am just giving month its picking up the year.

Output:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[atcodedog05]

Happy to help 🙂 @AyubKhan 

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂

[Qiu]

@AyubKhan 

If you like it mark it as accept please😂
multiple accept is allowed.