Actual vs. Plan vs. Prior Year

[Stephaneky]

In 10.2.2 report studio I have a combination chart that displays Actual Sales against Plan but also includes Prior Year sales (see attachment).


Data comes from a virtual cube and both actual and plan respond appropriately showing values MTD for the last month displayed. For the prior year data, I use the following expression but I am unable to show pro-rated month values (i.e. the expression below returns value for the complete time period)


aggregate(tuple([Actual Face Amt],parallelPeriod([Sales].[Time].[Time_H].[Year],1,currentMember([Sales].[Time].[Time_H]))) within set [CBU/Product Type Series])

To clarify the issue, the stacked bars represent sales by month, the line represents plan for the same period and the dot represents prior year sales for the same period. Both Bar and Line display pro-rated values (i.e. on the 5th of July, sales & plan are pro-rated to 5 days, but the prior year value is displaying prior year sales for the entire month of July in the prior year).

I need to modify the expression to pro-rate the prior year data to do the same (i.e. only show sales up to the 5th of July of the prior year).

Any help would be greatly appreciated.

[sdf]

first idea is to use add-years([Sales].[Time].[Time_H],-1)

have not tried but this comes first to my mind.

[Stephaneky]

Thank you sdf,

Isn't the yearly offset taken care of by this part of the expression?

parallelPeriod([Sales].[Time].[Time_H].[Year],1

Are you suggesting I incorporate your addition to the second argument?  currentMember([Sales].[Time].[Time_H])))

[sdf]

first of,

what's this item "parallelPeriod([Sales].[Time].[Time_H].[Year],1,currentMember([Sales].[Time].[Time_H])))" output?
I am guessing it only results to year and month. Thus why you are getting the total of that month instead of the pro rated day.

this is what I have in mind, if [Sales].[Time].[Time_H] returns full YYYY-MM-DD.

aggregate(tuple([Actual Face Amt],add-years([Sales].[Time].[Time_H],-1)) within set [CBU/Product Type Series])

you can try this.

[Stephaneky]

I appreciate the input sdf...

I am getting this error when modifying the expression

The argument at position 2 of 'tuple' from the data item 'Prior Year Actuals for Months' is a 'value' expression, which cannot be coerced into a 'member' expression

[sdf]

are you on DQM?

[Stephaneky]

yes we are on DQM.