locked
Diff between two years RRS feed

  • 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

    Thursday, February 9, 2017 1:36 PM

All replies

  • 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

    Thursday, February 9, 2017 3:42 PM
  • 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 MSDNFSF@microsoft.com

    Friday, February 10, 2017 2:47 AM
  • 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

    Tuesday, February 14, 2017 8:26 PM
  • 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 MSDNFSF@microsoft.com

    Wednesday, February 15, 2017 2:14 AM