# MDX - Sum by a specific dimension

### Question

• HI,

In my cube browser, I am showing the below results:

From Date          To Date                Amount               Sum Amount per From Date – Needed one

2018-05-01          2018-05-02         100                        1372

2018-05-01          2018-05-04         63                           1372

2018-05-01          2018-05-05         45                           1372

2018-05-01          2018-05-10         412                        1372

2018-05-01          2018-05-17         752                        1372

2018-05-06          2018-05-11         400                         2770

2018-05-06          2018-05-15         752                        2770

2018-05-06          2018-05-16         4                             2770

2018-05-06          2018-05-20         754                        2770

2018-05-06          2018-05-21         12                           2770

2018-05-06          2018-05-26         782                        2770

2018-05-06          2018-05-27         66                           2770

How to create a calculated member to show the column ‘’Sum Amount per From Date’’?

Thanks a lot!
Wednesday, June 13, 2018 3:24 PM

• WITH MEMBER [Measures].[Sum Amount per From Date]

AS

([Measures].[Amount],[DimDate].[To Date].[All])

Ousama EL HOR

[If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

[User Page]     [MSDN Page]     [Blog]     [Linkedin]

Wednesday, June 13, 2018 3:33 PM
• Hi Karim,

I assume From Date and To Date are role playing dimension in your cube, then you might want to try blow MDX expression:

```[Measures].[Sum Amount per From Date] AS
(Root(([To Date].[To Date].[All],
[From Date].[From Date].currentMember)),
[Measures].[Amount])

OR

[Measures].[Sum Amount per From Date] AS
([To Date].[To Date].[All],
[Measures].[Amount])
```
See below MDX query tested in cube AdventureWorks:
```With
Member [Measures].[AmountIngoreCAT] As
( [Product].[Category].[All],
[Measures].[Reseller Sales Amount])

Member [Measures].[AmountIngoreCAT1] As
(Root(([Product].[Category].[All],[Date].[Date].currentMember)),
[Measures].[Reseller Sales Amount])

select  {[Measures].[Reseller Sales Amount],
[Measures].[AmountIngoreCAT],
[Measures].[AmountIngoreCAT1] }  on 0,
non empty {[Date].[Date].[Date]*
[Product].[Category].[Category]}on 1
where
[Date].[Calendar Year].&[2012]```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Thursday, June 14, 2018 1:58 AM

### All replies

• WITH MEMBER [Measures].[Sum Amount per From Date]

AS

([Measures].[Amount],[DimDate].[To Date].[All])

Ousama EL HOR

[If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

[User Page]     [MSDN Page]     [Blog]     [Linkedin]

Wednesday, June 13, 2018 3:33 PM
• Hi Karim,

I assume From Date and To Date are role playing dimension in your cube, then you might want to try blow MDX expression:

```[Measures].[Sum Amount per From Date] AS
(Root(([To Date].[To Date].[All],
[From Date].[From Date].currentMember)),
[Measures].[Amount])

OR

[Measures].[Sum Amount per From Date] AS
([To Date].[To Date].[All],
[Measures].[Amount])
```
See below MDX query tested in cube AdventureWorks:
```With
Member [Measures].[AmountIngoreCAT] As
( [Product].[Category].[All],
[Measures].[Reseller Sales Amount])

Member [Measures].[AmountIngoreCAT1] As
(Root(([Product].[Category].[All],[Date].[Date].currentMember)),
[Measures].[Reseller Sales Amount])

select  {[Measures].[Reseller Sales Amount],
[Measures].[AmountIngoreCAT],
[Measures].[AmountIngoreCAT1] }  on 0,
non empty {[Date].[Date].[Date]*
[Product].[Category].[Category]}on 1
where
[Date].[Calendar Year].&[2012]```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Thursday, June 14, 2018 1:58 AM
• Thanks a lot!

Friday, June 15, 2018 4:31 PM

I am still stuck at my last step, I am trying to create a calculated member showing totals per date from but I should pass through Date to first like below:

 DateFrom DateTo DateDiff Amount1 DateDiff*Amount1 TotalPerDateFrom 01/05/2018 03/05/2018 2 300 600 1050 01/05/2018 04/05/2018 3 150 450 06/05/2018 09/05/2018 3 33 99 224 06/05/2018 11/05/2018 5 25 125

but when i remove the Date to the concept changed as per below :

 DateFrom AVGDateDiff Amount1 DateDiff*Amount1 01/05/2018 2,5 450 1125 (wrong) 06/05/2018 4 58 232 (wrong)

How to calculate [date diff] * [amount1] grouped by [date to] before the sum by [Date from]

Thanks a lot!