Hi, i am new to MDX query could anyone help me in determining cummulative revenue...i have written that in SQL but i need the same in MDX form...

idRevenue Cummulative Revenue
12020
23050
31060
440100
520120

Select a.id,
last(revenue),
sum(revenue) as cummRev
from
order a
inner join
order b
on
a.id >= b.id
where
a.id < 5
group by
a.id

Well, it depends on how this data is modelled in a cube. Assuming that there is an [Orders] dimension with an [OrderID] attribute, and there is a [Revenue] measure, something like:

Code Snippet

With

Member [Measures].[CumRevenue] as

Aggregate({NULL:[Orders].[OrderID].CurrentMember}, [Measures].[Revenue])

Select

{[Measures].[Revenue], [Measures].[CumRevenue]} on 0,

[Orders].[OrderID].[OrderID].Members on 1

from [OrdersCube]

• Proposed as answer by manishcal16PPS 15 minutes ago

Hi,

Deepak is right, if you want to test on advWork,

With

Member [Measures].[CumRevenue] as

Aggregate({NULL:[Product].[Product].CurrentMember}, [Measures].[Internet Sales Amount])

Select

{[Measures].[Internet Sales Amount], [Measures].[CumRevenue]} on 0,

[Product].[Product].[Product].Members on 1

from [Adventure Works]

Regards,

Manish