Hi,
Does anyone have an idea how to make a pivot table with a horizontal dimension of "mmm-yy (month-year)", that needs to show me figures for the last 12 months YTD figures (accumulated) (based on the current selection in the calendar).
(selected : Year = 2019, month = mar)
Notice, that i have to make YTD figures across 2 years, and further more only show the last 12 months…
Look at the attached xlsx file …
I have tried with :
(
IF(ColumnNo()<=12,
RANGESUM(
before(
////////// The whole last year ytd acc //////
(
SUM( {< Calendar_Date.Month =,[Calendar_Date.Year Months MMMM-YY] = , Calendar_Date.Year = ,
Calendar_Date.Date = {">=$(=yearstart(date(addmonths(max(Calendar_Date.Date),-12))))<=$(=yearend(date(addmonths(max(Calendar_Date.Date),-12))))"} >} LED_AMOUNTMST)
+
SUM( {< Calendar_Date.Month =,[Calendar_Date.Year Months MMMM-YY] = , Calendar_Date.Year = ,
Calendar_Date.Date = {">=$(=yearstart(date(addmonths(max(Calendar_Date.Date),-12))))<=$(=yearend(date(addmonths(max(Calendar_Date.Date),-12))))"} >} PRIMO_BALANCE_TY)
)
,0,12
)
)
,
rangesum(
before(
(
SUM( {< Calendar_Date.Month =,[Calendar_Date.Year Months MMMM-YY] = , Calendar_Date.Year = ,
Calendar_Date.Date = {">=$(=yearstart(date(addmonths(max(Calendar_Date.Date),-0))))<=$(=monthend(date(addmonths(max(Calendar_Date.Date),-0))))"} >} LED_AMOUNTMST)
+
SUM( {< Calendar_Date.Month =,[Calendar_Date.Year Months MMMM-YY] = , Calendar_Date.Year = ,
Calendar_Date.Date = {">=$(=yearstart(date(addmonths(max(Calendar_Date.Date),-0))))<=$(=monthend(date(addmonths(max(Calendar_Date.Date),-0))))"} >} PRIMO_BALANCE_TY)
)
,0,$(=num(month(date(max(Calendar_Date.Date))))) // the month number of the max selected date
)
)
)
)
all the above will show me the entire last year ytd acc + the first x month of the next year (based on the max date).. BUT, need to see the last 12 months back based on the current selection. So I am currently seeing to many months… (the above works apart from that )..
I know i could go the "Script way" by making YTD aggr… But if I can, I would like to avoid it..
Regards,
Anders
