Hello everybody,
I have an issue with an appplication I am developing and I hope you can help me.I have a straight table with a dimension (Rolling last 12 months, Feb.2016 till Feb.2017). This dimension has been calculated as follows:
aggr(only({<[Calendar Start Date.Date]={">=$(=date(Monthstart(AddMonths(today(),-12))))<=$(=date(Monthend(today())))"}>}[Calendar Start Date.YearMonth]),[Calendar Start Date.Year],[Calendar Start Date.YearMonth])
And 3 expressions:
1. Shows the Share of Market for feb2016 till feb 2017:
IF([Category] = 'RMC',
SUM({<[Calendar Start Date.Date]={">=$(=date(MonthStart(AddMonths(today(),-12))))<=$(=date(Monthend(today())))"}>}[Sales.Units]),
SUM({<[Calendar Start Date.Date]={">=$(=date(MonthStart(AddMonths(today(),-12))))<=$(=date(Monthend(today())))"}>}[Sales.Packs]))
/
IF([Category] = 'RMC',
SUM({<[Brand Family Name], [Product Name], [Calendar Start Date.Date]={">=$(=date(MonthStart(AddMonths(today(),-12))))<=$(=date(Monthend(today())))"}>}[Sales.Units]) ,
SUM({<[Brand Family Name], [Product Name], [Calendar Start Date.Date]={">=$(=date(MonthStart(AddMonths(today(),-12))))<=$(=date(Monthend(today())))"}>}[Sales.Packs]))
2. Shows the Share of Market for YAGO feb2015 till feb2016
IF([Category] = 'RMC',
sum(aggr(SUM({<[Calendar Start Date.Date]={">=$(=date(MonthStart(AddMonths(today(),-24))))<=$(=date(Monthend(AddMonths(today(),-13))))"}>}[Sales.Units]), [Calendar Start Date.MonthName])),
sum(aggr(SUM({<[Calendar Start Date.Date]={">=$(=date(MonthStart(AddMonths(today(),-24))))<=$(=date(Monthend(AddMonths(today(),-13))))"}>}[Sales.Packs]), [Calendar Start Date.MonthName])))
/
IF([Category] = 'RMC',
sum(aggr(SUM({<[Brand Family Name], [Product Name], [Calendar Start Date.Date]={">=$(=date(MonthStart(AddMonths(today(),-24))))<=$(=date(Monthend(AddMonths(today(),-13))))"}>}[Sales.Units]),[Calendar Start Date.MonthName])) ,
sum(aggr(SUM({<[Brand Family Name], [Product Name], [Calendar Start Date.Date]={">=$(=date(MonthStart(AddMonths(today(),-24))))<=$(=date(Monthend(AddMonths(today(),-13))))"}>}[Sales.Packs]),[Calendar Start Date.MonthName])))
3. Difference between both
I have managed to aggregate the Share of Market for YAGO for all months except for the current month I am having troubles to be able to bring the YAGO for the current month (Feb.2017).
