If I understand what you say then the problem will be resolved if you add a financial year field to your calendar. Try something like this in your calendar script:
if (Ceil(Month(Date) / 3)=1,
Year(Date)-1 & '/' & Year(Date),
Year(Date) & '/' & Text(Year(Date)+1) )
I'm assuming that your financial year will run from 1st Apr.
Hope this helps
Thanks for your reply, really appreciate the help.
i have already loaded in FY and Periods in my master calendar. So they exist as fields in my dashboard but when I try to change my expression away from PeriodIndex to an FY and Period based one I cannot get it to work.
In an ideal world i would just code in:
but obviously this wont work as an expression without an error. Also as my PeriodIndex goes further back than my calendar figures for FY and Period I am wondering will it be an issue performing the summing everything less than function.
Do you really need a Period field as well as a PeriodIndex field? Aren't both numerical indexes for calendar months? Unless your financial years start from 6th April or another date that is not a MonthStart any numerical index for a month will serve our purpose.
Of course it could be that I've missed something. It wouldn't be the first time.
Sorry about how difficult this is to explain and unfortunately I am not able to upload a qvw of my problem due to confidentiality issues and my desire to not be imprisoned !!
Re your last post:
i am trying to phase out PeriodIndex alright to an FY and Periods Calendar. Right now they are linked, i.e when I select a period it matches up with the corresponding PeriodIndex.
However when my expression for my opening balance figure is based on PeriodIndex and i select a from the field of PeriodIndex I get correct values.
But when I select a FY and a Period I obtain values that are not correct.
So when working correctly it resembles this:
And when not working correctly it resembles this:
Hopefully these Images better explain my problem. Also to remind my current expression is:
Thanks again Andrew!