How to compare two dataset from same fact table

[erwink]

I've using Data Manager created a DMR fact table.

To simplify let's take this example  Dimension Product and Dimension Time, Measure as planned sales per month
I need to be able to compare month planed sales, with those planned last month, in one crosstab
So I created a table as

ProductID,MonthID, Value, Capture_Date
101, 201603, 25, '2016-08-09 17:06:23.000'
101, 201603, 15, '2016-08-31 06:36:52.000'

1) Crosstab with one query doesn't work , shows for both capture_date 40
2) union of two queries. Works in View tabular data, but in crosstab ==> RQP-DEF-0502 Master dataset can not be used for the provided query

to 2) found on the net that union/DMR/Crosstab will not work ?!

How can I achieve what I want?

Thank you

[AnalyticsWithJay]

Try a case statement for each measure, in the same query:

Last Month Planned Sales (Pseudo Code)

Code Select Expand


CASE WHEN CURRENT_MONTH
THEN 0
ELSE PLANNED_SALES
END



Current Month Planned Sales (Pseudo Code)

Code Select Expand


CASE WHEN LAST_MONTH
THEN 0
ELSE PLANNED_SALES
END


The aggregation I assume is Total.

What we're doing is we're substituting a 0 for the measure when it's not for the month that you want. When the rollup occurs, you ensure that you're only adding values from last month or current month, depending on the calculation.