How to find the Fiscal Quarter End Date?

Hello all,

Good Day!

I have the requirement to find the aging as of Fiscal Quarter End, Could you help me to find the solution.

I tried the below formula but it showing quarter-end as March but my actual quarter-end was April. Please let me know your comments.

DateTimeTrim(Left([Due Date],5) +
PadLeft(ToString(Ceil(DateTimeMonth([Due Date])/3)*3),2, '0')
+ '-01', "lastofmonth")

alteryx Douts.xlsx

Developer Tools

Input

Output

Custom Tools

Date Time

Common Use Cases

Transformation

Dynamic Processing

Preparation

Data Investigation

Time Series

Accepted answers

See attached for the updated WF.

How to find the Fiscal Quarter End Date.yxmd

Hi @pedrodrfaria

Thank you so much for your help.

Here is my solution based on your formula,

1. Finding the Fiscal Quarter

IF
DateTimeFormat(DateTimeToday(),"%B") = "February" OR
DateTimeFormat(DateTimeToday(),"%B") = "March" OR
DateTimeFormat(DateTimeToday(),"%B") = "April"

THEN "April"

ELSEIF
DateTimeFormat(DateTimeToday(),"%B") = "May" OR
DateTimeFormat(DateTimeToday(),"%B") = "June" OR
DateTimeFormat(DateTimeToday(),"%B") = "July"

THEN "July"

ELSEIF
DateTimeFormat(DateTimeToday(),"%B") = "August" OR
DateTimeFormat(DateTimeToday(),"%B") = "September" OR
DateTimeFormat(DateTimeToday(),"%B") = "October"

THEN "October"

ELSEIF
DateTimeFormat(DateTimeToday(),"%B") = "November" OR
DateTimeFormat(DateTimeToday(),"%B") = "December" OR
DateTimeFormat(DateTimeToday(),"%B") = "January"

THEN "January"

ELSE ""

ENDIF

2. EOQ Date Parse

DateTimeAdd(DateTimeParse([EOQ Month],"%B"),DateTimeYear(DateTimeToday())-DateTimeYear(DateTimeParse([EOQ Month],"%B")),'years')

3. EOQ End Date

datetimeadd(DateTimeAdd([EOQ Start Date],1,'month'),-1,'day')

4. Date Difference with my Due Date

DateTimeDiff([EOQ End Date],[Due Date],"days")

Output

I'm sorry I should not share my original data sources and this works for me.

All comments

Can you please explain the logic you are trying to apply?

Hi @pedrodrfaria

My Logic is very simple. I'm trying to find the aging "as of today" and the "end of the Fiscal Quarter Date" with my Due-Date.

Please refer to the below newly added columns "Age" and "Age EOQ".

Quick Links

Tags

This months top contributors

atcodedog05 19598

Qiu 15875

MarqueeCrew 13708

apathetichell 13703