Find a specific date

Hi, I have a list of data that has calendar dates in it historically. I'm looking to identify the start of the most recent Fiscal Year in the data and have run into a wall. Fiscal year start if always November 1. Is there a date function that will allow you to get the last day of the previous year and I can go back 2 months and add 1 day?

Date
1-Jan-17
1-Feb-17
1-Mar-17
1-Apr-17
1-May-17
1-Jun-17
1-Jul-17
1-Aug-17
1-Sep-17
1-Oct-17
1-Nov-17	Find this date
1-Dec-17
1-Jan-18
1-Feb-18
1-Mar-18
1-Apr-18
1-May-18
1-Jun-18
1-Jul-18
1-Aug-18
1-Sep-18

Preparation

Accepted answers

neilgallen

@nogap2

A bit more direct formula would look like this:

datetimeadd(datetimeadd(datetimeadd(datetimeparse(datetimetoday(),"%Y"),-1,"days"),-2,"months"),1,"days")

The datetimeparse will give you the current year, defaulted to January 1. From there you're just subtracting and adding dates per your request above (subtract one day, go back two months, then add a day).

If you're always trying to get back to Nov 1, then you could be simpler with just

datetimeadd(datetimeparse(datetimetoday(),"%Y"),-2,"months").

All comments

Kenda

Hi @nogap2!

Because the date you're looking for is always the same date, could you just filter on that date without using date functions/calculations? By this, I mean just use a Filter that says

contains([Date],"1-Nov")

tcroberts

I'm not sure exactly what the complications are here, but if I were to approach this problem, I'd probably try and extract out a year from the dates, which you can do with DateTimeFormat(DateTimeParse([Date], '%d-%b-%y'), '%Y%")

DateTimeParse turns the field into the Alteryx formatted Date, and DateTimeFormat selects out the 4 digit year (%Y), can get 2 digit year from (%y).

You could then do something like:

IF DateTimeParse([Date], '%d-%b-%y') = DateTimeParse([Year]+'-11-01', '%Y-%m-%d')

THEN 1

ELSE 0

ENDIF

to tag the rows which contain the 1st of November.

I there's some reason you can't use something like this please let me know,

Cheers!

EDIT: @Kenda's solution is much quicker and easier without unnecessary computations or saving of data.

nogap2

Thanks for the help!

It may be a little convoluted way of going about it, but I was able to get it working through the DateTime functions.

First I found the Fiscal Year End with:

IF [Month] >=11 THEN
DateTimeAdd(DateTimeAdd([Calendar Year Start],+22,"Months"),-1,"Days")
ELSE DateTimeAdd(DateTimeAdd(DateTimeAdd(DateTimeTrim([Date],"Year"), 1, "Year"), -2,"Months"),-1, "Day")
ENDIF

Then I went back one year less one day

I ended up needing both Fiscal Year end and Start in my formulas so it worked out.

DateTimeAdd(DateTimeAdd([Fiscal Year End],-1,"Year"),1,"Day")

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703