Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have to calculate fiscal YTD? Our fiscal year starts from July to August. So, If i select Month = May 2012..then it has to calculate from July 2011- May 2012. I have the following formula, but the moment I select time dimensions, it filters that data to that month.
=if(date(CalMonthYear, 'M')>= 7,
sum ( {< CalMonthYear={">=$(=makedate(year(max(CalMonthYear)), num('7'))) <=$(=Max(CalMonthYear))"}, CalMonthYear=>} Direct_Cost),0)
Any Help?
Thanks.
May be like attached sample?
Hi Vani,
Can you calculate the Quarter Start Date and End Date in two variables and use it inside ur expressions.
The issue is the > expression always returns month Jul since you have used Num('7'), also the use of Max in < will always returns the selected month and thus your chart shows incorrect values.
If u wish to try put two labels and put the expressions and check.
Try using the below expression
sum ( {< CalMonthYear={">=$(=QuarterStart(CalMonthYear) <=$(=QuarterEnd(CalMonthYear))"}, CalMonthYear=>} Direct_Cost)
Thanks...
Thanks Micheal.
Since our fiscal year starts from July- I always want it to calculate from July till the month I selected. So,
1)if I select April 2012 then it has to calculate from May 2011-April 2012.
2) If I select October 2012. The it has to calculate from July 2012-October 2012.
Any Help.
Thanks
Could you post a sample?
Hi,
Here is the sample file. If I select March 2013, it should show data only from July 2012-March 2013. This is my formula:
=if((only({<FiscalFlag=, CalMonthYear=>}FiscalFlag)= 1),
sum ( {< CalMonthYear={">=$(=makedate(year(max(CalMonthYear)), num('7'))) <=$(=Max(CalMonthYear))"}>} Direct_Cost),
if(only({<FiscalFlag=, CalMonthYear=>}FiscalFlag) = 0,
sum ( {< CalMonthYear={">=$(=makedate(year(max(CalMonthYear)-1), num('7'))) <=$(=Max(CalMonthYear))"}>} Direct_Cost)
))
I am flagging the months which are greater than 7 as 1 and less than 7 as 0 and using that FiscalFlag to calculate.
Thanks,
May be like attached sample?
Awesome...Thanks It worked. I was making it way too complicated than it required.
Thanks.