Identifying Number of Days in a Given Period for Each Fiscal Year

[jeffr3]

Hi,

I'm trying to identify the following:

I have a start date column and an end date column. I need to find the number of days falling into each Fiscal Year range. FY goes from 06.01 - 05.31 for every year (2020.06.01 would be first day of FY21).

For example, if a given row has a start date of 05.30.2020 and end date of 06.03.2020, then I need it to spit out FY20 # days = 2 and FY21 # days = 3.

Is this possible?

Thanks!

[Shifty]

To answer your question "Is it possible?"... this is Alteryx, of course it's possible.  

Whether I can help you is another question!  I've given it a bash all the same - see attached workflow.  

Essentially we start with an input dataset which is similar (at least I hope) to what you've described in your question.  I've then converted the two date columns to date formats, generated a row for each date between the start date and the end date and then used a Formula tool to determine the fiscal year of each.

Hope this helps!  If not, I will call in the cavalry for you (@mceleavey  @TheOC)

Shifty

PS/ year end looming? 😁

[AngelosPachis]

Hi @jeffr3 ,

I would use a generate rows tool to generate all dates between your start and end date, and then with a formula tool I would flag the fiscal year they belong to

Hope that helps, let me know if that worked for you or if you have any question on the workflow.

Cheers,

Angelos

[jeffr3]

Hi! Thanks for this. Would it be possible to have the data in one row? I'm looking across 3 FYs, and for each row containing two dates, I need Alteryx to spit out 3 columns calculating the # days falling into each FY.

Really appreciate your help!

[Shifty]

No worries, @jeffr3.  If you need the number of days in each year so you can apportion costs etc. then you can just add a Summarize tool and group by Fiscal:

Cheers,

Shifty

[Shifty · Accepted solution]

Having re-read your follow up question, @jeffr3, I've tried something a little different to get you what you need.

It's a little more complicated now but I think gets you the breakdown you're after:

Let me know if this helps!

Shifty

PS/ 2020 was a leap year hence the 366. 

PPS/ if this helps please mark the response as a solution. Cheers!

[apathetichell]

@AngelosPachis  awesome solution - I got caught up in weeds of of the datetimeformat %j qualifier... one quick thing - a summarize tool at the end might be easier for @jeffr3 

[TheOC]

Cool solution @Shifty ! Glad you didn't have to call in the backup 😁

[mceleavey]

Perfect solution, @Shifty 

Glad you didn't have to distract me from my unbelievably thrilling and comprehensive social life.

M.

[jeffr3]

This is exactly what I needed. Thank you so much!