Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am facing a challenge that I have a table in which there are sales according to the Launch Year. We have Fiscal Year, Month ,Quarters Sales & Forecast sales in this table.I have a requirement that in a pivot table, [Current year and last year] sales should be calculate as per selected Month Year dimension.
Suppose I have selected FY15 then Current year sales & Previous Year sales should be populate for FY [Jul 14-Jun 15].
I have already created that chart using variable but when I am using that variable a new Fiscal year FY14 rows also populated for FY14 month-year dimension which is not required . I want that one year column should only populate not the second year column should populate. Prior Year sales should populate parallely with current Year Month-Year with Current Year sales.
I have attached the data & Requirement also.
Would appreciate if anyone could help me.
Hi Rishi
I have created the attached document, which might be useful.
It handles This Year, LY, Rolling 12, YTD, YTD LY, and same Month LY.
Please note that your data needs to be sorted correctly, YearMonth must be sorted Ascending in the datatable (this can be done in your calendar table), for this to work (required by Aggr)
You can probably alter the expressions to suit your data.
An alternative could be using As-Of dates (you can find plenty of information on how to do this on the community)
//Martin