Skip to Content

Fiscal Year to date across multiple fiscal years

Mar 12 at 03:16 PM


avatar image
Former Member

I am looking at how to create a recurring report which has fiscal year to date across multiple years.

I already have a fiscal year dimension to split up the years, but am looking to show current actuals compared to years past up to the current date in past years.

Example: run date 3/12/18

graph would show:

7/1/17 - 3/12/18

7/1/16 - 3/12/17

7/1/15 - 3/12/16

Each week the end date would be the run date


10 |10000 characters needed characters left characters exceeded


You need couple of formulas here:

Capture User response (PromptVal): Userresponse("your prompt name").

Current Year :

=FormatNumber((ToNumber(Userresponse("your prompt name")));"####") OR

= FormatNumber((ToNumber([PromptVal]);"####")

Previous Year:

FormatNumber((ToNumber([Current Year])-1);"####")

Previous Year-1:

FormatNumber((ToNumber([Current Year])-2);"####")

post back your results.



Former Member

Thank you.

I tried this however it will not return what I was looking for. The user prompt isn't necessary, I think I confused the situation by saying run date. I meant if the report runs on that date, such as an automated recurring report.

The graph I am trying to change is:

You notice that fiscal 2017 is much higher than 18, since 18 is till in progess. I want each year on this graph to show only up until the date in its respetive year when the report is run. If I run the report today, 2017 would only show its data to today in 2017.



Instead of your prompt, try use CurrentDate().

Previous Year CurrentDate:

RelativeDate(CurrentDate(); - 365)

This blog might give you more details of what you are looking for:



* Please Login or Register to Answer, Follow or Comment.

0 Answers