Fiscal Year Starts from October to September Data file provided only has Fiscal Year and Month along with few Categories and Amount. I need to get Fiscal Year to Month level data Fiscal YearMonthSum AmountFY YTD(Month - since input file only has month)FY19Apr4031.114031.11FY19May1293883.781297914.9FY19Jun55649.61353564.5FY19Jul73008.91426573.4FY19Aug21853.381448426.8FY19Sep-524.081447902.7FY20Oct1905250.621905250.62FY20Nov183154.722088405.34FY20Dec6270.152094675.49FY20Jan12462.862107138.35FY20Feb44379.892151518.24FY20Mar49065844.5251217362.8FY20Apr7350942.8258568305.6FY20May49489202.01108057508FY20Jun233368.61108290876FY20Jul183584.37108474461FY20Aug-3502.22108470958FY20Sep 108470958 Alt Sample Data.xlsx

Need help with calculating Fiscal Year to Date(month)

Fiscal Year Starts from October to September

Data file provided only has Fiscal Year and Month along with few Categories and Amount.

I need to get Fiscal Year to Month level data

Fiscal Year	Month	Sum Amount	FY YTD(Month - since input file only has month)
FY19	Apr	4031.11	4031.11
FY19	May	1293883.78	1297914.9
FY19	Jun	55649.6	1353564.5
FY19	Jul	73008.9	1426573.4
FY19	Aug	21853.38	1448426.8
FY19	Sep	-524.08	1447902.7
FY20	Oct	1905250.62	1905250.62
FY20	Nov	183154.72	2088405.34
FY20	Dec	6270.15	2094675.49
FY20	Jan	12462.86	2107138.35
FY20	Feb	44379.89	2151518.24
FY20	Mar	49065844.52	51217362.8
FY20	Apr	7350942.82	58568305.6
FY20	May	49489202.01	108057508
FY20	Jun	233368.61	108290876
FY20	Jul	183584.37	108474461
FY20	Aug	-3502.22	108470958
FY20	Sep		108470958

Alt Sample Data.xlsx

Date Time

Best Practices

Batch Macro

Documentation

Accepted answers

RolandSchubert

Hi @Poojaraj123 ,

the problem is, that Running Total only considers "existing" months, i.e. if there are values for Apr and May, there will be a Running Total for Apr and May only, but not for Jun, Jul and so on. I've added additional rows for the "missing" months to fill the gaps.

Let me know if it works for you.

Best,

Roland

New Workflow9-1.yxzp

All comments

echuong1

You can use a Summarize tool to group all of your data on a fiscal year-month basis since you have multiple divisions and categories. To derive the YTD, you can use the Running total tool. Prior to this, I used a reference table, join, and a sort tool to put the months in the correct fiscal order.

Hope this helps!

New Workflow9.yxzp

Poojaraj123

When I summarize by Year month and amount it works but in my Target Report I also need to be able to filter by Division and Category to be able to see Fiscal YTD by month based on the Division and Category filters and not including then in the Summarize removes the fields from the output file.

So when i add Category and Division in the Summarize, the running total gets messed up. How do i fix that?

2020-08-11_1-14-54.jpg

2020-08-11_1-17-39.jpg

Poojaraj123

So when i add Category and Division in the Summarize, the running total gets messed up. How do i fix that?

2020-08-11_1-17-39.jpg

2020-08-11_1-14-54.jpg

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703