YTD Formula

[gautik92]

I have a line chart where ill be having month as dimension

I need to show 24 months from aug back to 24 months

Measure is YTD

any idea?

[Anil_Babu_Samineni]

May be this? and you can use which field you want degrade.

Sum({<DateField = {">=$(=AddYears(Max(Month),-2)) <=$(=Max(Month))"}>}Sales)

[arvind_patil]

Hi Gowtham,

May be like this:

Sum({<Date= {">=$(=Addmonths(Max(Month),-24)) <=$(=Max(Month))"}>}Value)

Thanks,

Arvind Patil

[gautik92]

this will select the date but wat abt ytd in this formula

[Anil_Babu_Samineni]

You can simply define the YearStart() in your first operator

Sum({<DateField = {">=$(=YearStart(AddYears(Max(Month),-2))) <=$(=Max(Month))"}>}Sales)

[Anil_Babu_Samineni]

Or

Sum({<DateField = {">=$(=YearStart(AddYears(Max(DateField),-2))) <=$(=Max(DateField))"}>}Sales)

[gautik92]

If I use this it will take year start I don't want like that ur first expression looks good it fetches date from aug 15 to july 17 but I need to sum the data lik for aug 15 I need to sum data from april 15-august15

same like for all the months

[Anil_Babu_Samineni]

If second expression doesn't help then why do you call name it as YTD? Do you know the definition of YTD? And you says, you need last 24 months? But here for Aug 15 it is indicating to me 5 months data only when select on month as Aug 15. I am not sure, I followed you this moment

[gautik92]

Hi anil thanks for your reply

I have attached a data please check

in that ill be having sum in the second column and third column is having ytd

if you see the third column till apr 15 the value will be fine but after apr 15 its summing up with the old data

but my output should be like if its apr 15 it shud show sum(Leaver)and may shud sum up apr 15 and may 15 and so on .Please  check the file I have attached.

my third column formula is

=IF(Period1=YearStart(Period1,0,4),Sum(VOLUNTARY_LEAVER),RangeSum(Above(Sum(VOLUNTARY_LEAVER),0,12)))

[Anil_Babu_Samineni]

If you are using straight table use Full Accumulation radio button to achieve.

If it is in pivot table then use like below

Rangesum(

     Below(

          Sum({<DateField = {">=$(=YearStart(AddYears(Max(Month),-2))) <=$(=Max(Month))"}>}Sales)

               ,0),

          Above(Sales,1,RowNo(TOTAL)))