Diff between two years

Question

Hello All,

I want compare same measure between two years in my cube.

Datas : 

COMPANY    -    TURNOVER      -   FISCAl YEAR

COMPANY1  -    13    -   2016

COMPANY1  -    10    -   2015

COMPANY2   -   20   -   2016

COMPANY2   -   20   -   2015

..... 

And I would the result

COMPANY1  = - 3 K€

COMPANY2 = - 3 K€

Anyone can help me?

Thx

DRO

Answer 1

Hi,

try something like this (it depends on your structure and how dynamic u want to build your calculation):

Create Member CurrentCube.[Measures].[Diff] as (ParallelPeriod([Date].[Date].[Year],1,[Date].[Date].currentmember),[Measures].[Turnover])-[Measures].[Turnover];

Kr Jürgen

Answer 2

Hi DRO2961,

Thanks for your question.
You may try following MDX query:

with
member [Measures].[Diff] as
  ([Measures].[TURNOVER]-
  (ParallelPeriod([Date].[Fiscal Year].[Fiscal Year],1,[Date].[Fiscal Year].currentmember),
  [Measures].[TURNOVER]))
select [Measures].[Diff] on 0 ,
{[COMPANY].[ COMPANY].[ COMPANY].members } on 1
From [mycube]
Where [Date].[Fiscal Year].&[2016]

See my sample MDX query below:

with
member [Measures].[previous Fiscal Year] as
(ParallelPeriod([Date].[Fiscal Year].[Fiscal Year],1,[Date].[Fiscal Year].currentmember),
[Measures].[Sales Amount])
member [Measures].[Diff] as
  ([Measures].[Sales Amount]-
  (ParallelPeriod([Date].[Fiscal Year].[Fiscal Year],1,[Date].[Fiscal Year].currentmember),
  [Measures].[Sales Amount]))
select {[Measures].[Sales Amount],[Measures].[previous Fiscal Year],[Measures].[Diff]} on 0 ,
{[Product].[Category].[Category].members } on 1
From [mycube]
where [Date].[Fiscal Year].&[2004]

---

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 [e-mail]

Answer 3

Hi Willson

It's perfect !

To complete your answer can you explain to me the best way to calculate percent ?

Thanks for your help

DRO

Answer 4

Hi DRO2961,

Since your original question has been resolved, please kindly mark the correct solution as an answer.In doing so, it will benefit all community members who are facing similar issues.

For the question "the best way to calculate percent", you may refer to these two blog :MDX ratio of "current parent" issue and Ratio to “Parent on Rows” in MDX .

---

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 [e-mail]