Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to create a sum to only add up figures based on the Month, and the relevant fiscal year.
The Fiscal year start on 1st June. So if a user selects "Sep 2014" they would see the sum of planned cost for 01/01/2014 > 30/09/2014
Where as if they select Jun 2015, the would see figures from 01/06/2015 - 31/06/2015
The user select using the list box created by the "MonthName" function so it reutrns the 3 letter month, and the year.
I have create a variable to select the first month in the fiscal year, but this doesn't appear to work, as if you select "Jan 2015" I would expect it to return 01/06/2014 as that was the start of the fiscal year "Jan 2015" is in. The variable is:
=addmonths(YearStart(Calendar.MonthName),5)
Any ideas? I see there are plenty of suggestions out there, and they appear to work if you are using standard calendar year.
The formula I'm using to get the sum is: sum({<Calendar.MonthName = {">=$(vFYTDStart)<=$(vFYTDEnd)"}>}Finacial_Data_Pln.Cost)
This is formula for vFYTDStart:
=IF(Month(Calendar.MonthName)<6,MakeDate(Year(Calendar.MonthName)-1,6,1),MakeDate(Year(Calendar.MonthName),6,1))