Last Year and Current Year Sales in Current Year Only

[rishikeshtiwari]

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.

[martin_dideriks]

    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