MDX to calculate difference between sales amount between 2 dates

Question

I have a Cube with a Date dimension hierarchy (Calendar,Year,Month,Week,Day) and Measure as SalesData, would like to add a calculated measure which would give me the SalesData for the selected date and the last date of the Previous Month.

Calendar Hierarchy is as follows

Date - Dimension 
Calendar - Hierarchy 
Year - [Date].[Calendar].[Year]
Quarter - [Date].[Calendar].[Quarter] 
Month - [Date].[Calendar].[Month] 
Week - [Date].[Calendar].[Week] 
Day - [Date].[Calendar].[Day]

Measures [Sales]

New Calculated Member need to be created - say [SalesMTD] Requirement is when a user select any date say 3 March 2016, the calculated member should give Sales as follows

[SalesMTD] = [Sales] on 3 March 2016 - [Sales] on 29 Feb 2016

Can someone please help me write an MDX query for the Calculated Measure ?

Your help much appreciated.

Answer 1

Please try the below code.

CREATE MEMBER CURRENTCUBE.[Measures].[Sales Last Month]

AS ([Date].[Months].CURRENTMEMBER.PREVMEMBER, [Measures].[sales])

, VISIBLE = 1  ;

Then write another calculation using the above calculations

CREATE MEMBER CURRENTCUBE.[Measures].[Sales Difference]

AS ([Measures].[Revenue]-[Measures].[Sales Last Month])

, VISIBLE = 1  ;

Answer 2

with member measures.SalesDataLastDaylasMonth
as
[Date].[Calendar].currentmember.firstsibling.lag(1)
//or [Date].[Calendar].currentmember.firstsibling.prevmember

select {measures.SalesData, measures.SalesDataLastDaylasMonth} on 0,
[Date].[Calendar].[Day].members on 1
from [Some Cube]

Here currentmember.firstsibling.prevmember fetches the member prior to the first day in the list of days in the current month.

Also, you can obviously create this member in a cube instead of having it query scoped like this. The syntax would be similar to the above answer.

Based upon your edit

There are multiple ways of getting to it. Below are some:

with member Measures.[SalesMTD]
as
Measures.[Sales]
-
(Measures.[Sales],Ancestor([Date].[Calendar].currentmember, [Date].[Calendar].[Month]).firstchild.firstchild)
//(Measures.[Sales],[Date].[Calendar].currentmember.parent.parent.firstchild.firstchild.lag(1))
//(Measures.[Sales],[Date].[Calendar].currentmember.parent.parent.firstchild.firstchild.prevmember)

select Measures.[SalesMTD] on 0,
[Date].[Calendar].[Day].members on 1
from [Some Cube]

The first approach is the neatest.