Fiscal Years in Xcelsius

other Xcelsius - Crystal Dashboard Design
1

Please can anyone advise on the Excel formula for displaying the Fiscal Year in a cell to enable it to be read in my Xcelsius model.

Fiscal period starts in April. If I want to show data for the last 12 months I want it to start from April 2009 to the current date September 2009).

I know the formula to get the current month but don’t know the code to use that will return the start month from which the data will be retrieved.

I thought about using a formula that would allow me to determine the data by the month ie if date = /September/** then cell would equal April **. If date = /February/ then cell would equal April ****-1.
I’m not very good with formulas but I was hoping that it could just read the date value. Maybe have current month in one cell and year in another cell and do a formula to determine what the start of the Fiscal period is?

I’m just rambling now. Hope someone out there can help me!
Thanks
Guinea_girl

guinea_girl (BOB member since 2008-12-11)

2

I have used the formula in this link to generate fiscal year range based on availble current range. This works for our requirement. Hope this will be useful to you. You will need to replace 7 in the formula to make the starting month as april.

Jeswin

Jeswin :india: (BOB member since 2009-07-01)

3

I would create a lookup table in the MS Excel portion within your Xcelsius model (2 columns: Calendar_MonthYear, Fiscal_MonthYear) and then use the MS Excel function VLOOKUP to map your Calendar_MonthYear to the Fiscal_MonthYear, finally pass this parameter to QaaWS, LiveOffice, etc.

Andreas :de: (BOB member since 2002-06-20)