Newbie Question for creating a this period/same period last year comparison
Hello. I'm just getting started in DeepSee, and am also a newbie in MDX.
I am looking to create a pivot table that will be used to drive a bar chart. The chart will have product classes across the bottom, and two bars per class: a calculated measure to derive Net Sales for the specified period last year, and a straight aggregation of a standard measure for the same period for this year. Ideally, this period can be chosen by the user via filters on the dashboard, and it could be week, month, or quarter. So, obviously, it's the calculated field (and perhaps the entire MDX query) I need help with.
My current query, which is just returning the product classes and the net sales (CLineExt), looks like this:
SELECT [Measures].[CLineExt] ON 0,NON EMPTY [WebCat1Desc].[H1].[WebCat1Desc].Members ON 1 FROM [INVOICEDETAIL]
The above cube, InvoiceDetail, also has an InvDate dimension, as well as 4 other dimensions derived from it: InvYear, InvQuarter, InvMonth, InvWeek
I *think* I need to use ParallelPeriod on any of these InvDate periods, but, as I say, MDX is still hard for me!
The result would be something like this:
July 2015 July 2016
Candy 867.00 589.00
Juice 3361.00 2663.00
Candy 393.00 293.00
The resulting chart have two sets of bars for each category, the first for the period for last year, the 2nd, for this year. Easy enough to do if the data can be gotten into the table above, but please note that I don't want a hardcoded formula (something like NOW.WEEK-52 for week, for example), rather, I am hoping to find something dynamic, that looks at the current period (set by filter) and then grabs the data from the same period, be it week, month, or quarter, from the pervious year.
P.S. If anyone has a dynamic LY/TY chart or Dashboard they want to share, to aid my learning, I'd be most grateful too!