Calculate Days in Financial Year, Annualized spend, Financial Year Spend

Hello,

I am trying to to figure out to calculate "Financial year between two dates", "No. of Days in specific Financial Year", "Annualized spend", "Spend related to specific Financial Year" & "Contracts that are expiring in next 90 days" (this has to be dynamic as we do the reporting every week).

Please note my financial year is 01-Nov-2019 to 31-Oct-2020.

I have attached the sample contracts report that i would need your help on. much thanks for all your help in advance.

Thanks

Subbu

Book1.xlsx

Topic of Interest

Accepted answers

SubbuSharma

Hi @DavidP,

That was superbly done and Alteryx numbers matches to my excel workbook. This automation will atleast save me 5 hours of work on a bi-weekly basis.

Thank you so much for all your help and patience. Magnificent work!

thank you David,

have a great one!

DavidP

The formula expects both Contract Term Days and Contract Value (CAD) to be numeric data types. It looks like one of them is a string.

You'll notice in my example workflow that I converted all amounts to fixed decimals and all whole numbers such as number of days to integers.

SubbuSharma

great stuff, had one the calc as string rather than fixed decimals.

thank you again.

All comments

DavidP

Hopefully this is a good starting point.

You have to decide if you need to include records that don't match between source1 and source2.

I've used DateTimeNow() for determining FY, days in current FY and expiry in 90 days.

I've you can provide more info on how to calculate the other fields, I can help you out with those.

contracts in FY.yxmd

SubbuSharma

Thank you, David. This is really helpful.

I have attached the dummy data (35 contracts) for which I am trying to calculate the highlighted fields in column E - I.

meanwhile, I'll play around with the workflow that you have provided. Much thanks again.

Dummy Data.xlsx

SubbuSharma

just to add, "Days in F20" column will have to calculate the below formula.

=IF(E6>=Dates!$A$2,0,IF(F6>Dates!$A$1,MIN(Dates!$A$2,F6)-MAX(Dates!$A$1,E6),0))

in other words

=if (start date>=31-Oct-2020,0,if(end date>31-Oct-2019,Min(31-Oct-2020,end date)-Max(31-Oct-2019,Start date)

SubbuSharma

attached excel file (refer tab "contract analysis") will give us the formulas that have been currently used to derive the numbers in columns K - N

Dummy Data.xlsx

DavidP

Hi @SubbuSharma, thanks for the clarifications.

I have replicated the logic in Alteryx based on the last dummy file with Excel formulas you sent.

Note that the column FY on which many of the calculations are based is dynamic and determined from the date that the workflow is run. This means that when you run it in the 1st Nov 2020, FY will switch to 21 automatically.

Let me know if you have any questions.

contracts in FY.yxmd

SubbuSharma

Hi @DavidP,

That was superbly done and Alteryx numbers matches to my excel workbook. This automation will atleast save me 5 hours of work on a bi-weekly basis.

Thank you so much for all your help and patience. Magnificent work!

thank you David,

have a great one!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703