Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need help in calculating Rolling 12 Months Logic for every Month in Pivot Chart.
I've explained my requirement in Attached Excel.
Maybe someone has faced this issue earlier. Would Appreciate if someone provide solution to this.
PFA.
Regards,
Erric
Hi Check this.
Sheet1:
LOAD *,Date(MonthTemp,'MMM-YY') as Month,Year(MonthTemp) as Year;
LOAD [New Month Year] as MonthTemp,
Amount_1 as Amount,
[Desired Output]
FROM
rolling.xlsx
(ooxml, embedded labels, table is Sheet1);
Use this:
=SUM({<Year,Month = {">=$(=Date(MonthStart(Max(Month),-11),'MMM-YY'))<=$(=Date(MonthStart(Max(Month)),'MMM-YY'))"}>}Amount)
Hi Arjun,
Thanks for the reply.
I actually tried something like that but its not working:
Sum({<[New Month Year] ={"$(='>=' & Date(AddMonths(Max([New Month Year]), -11), 'MMM-YY') & '<=' & Date(Max([New Month Year]), 'MMM-YY'))"}>} TOTAL Amount_1)
Also, the Logic shared by you is not working.
Regards,
Erric
I recommend using The As-Of Table if you are able to modify the script... or this
RangeSum(Above(Sum(Amount_1), 0, 12))
! !Hi
please find the attached app i got answer using strait table like below
=RangeSum( Below(sum(Amount_1), 0,12 ))
May be this expression
RangeSum(above(sum(Amount_1),0,12))
But if you select any monthyear in your listbox, then this will give only that monthyear amount.
But if you select any monthyear in your listbox, then this will give only that monthyear amount.
This can be addressed using this
RangeSum(Above(Sum({<MonthYear>}Amount_1), 0, 12)) * Avg(1)
Yes, this should work