none
MDX To Compare if Value Changed in Last 5 dates

    Question

  • Hi All,

    I am seeking help to see if we can compare data changes in last 5 days

    example:

    Date Dimension

    2018-05-18

    2018-05-17

    2018-05-16

    2018-05-15

    2018-05-14

    2018-05-13

    Sale Fact

    Date                SaleAmt

    2018-05-18      10

    2018-05-18      20

    2018-05-17      5

    2018-05-16      10

    2018-05-15      10

    2018-05-14      10

    2018-05-13      10

    Based on above data we have values changed between 16-17 & then 17-18. If values have changed then I want to say it has changed or mark those values in different color

    below query does give me data for last 5 days

    SELECT 
    Tail(
    Descendants
    (
    [Date].[Calendar].CurrentMember, [Date].[Calendar].[Date]
    )
    ,5
    ) on 0,
    [Measures].[Sale Amt] on 1
    FROM
    [CUBE];


    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Friday, May 18, 2018 7:21 AM

Answers

  • Hi,

    Try this query :

    WITH MEMBER [CurrentDate] AS ([Date].[Calendar].CurrentMember,[Measures].[Sale Amt])
    MEMBER [PreviousMonth] AS ([Date].[Calendar].PrevMember,[Measures].[Sale Amt])
    MEMBER [Changed] AS IIF([CurrentDate]<>[Changed],"Changed",'No change')
    
    SELECT 
    Tail(
    Descendants
    (
    [Date].[Calendar].CurrentMember, [Date].[Calendar].[Date]
    )
    ,5
    ) on 0,
    {[Measures].[Sale Amt],[Changed]} on 1
    FROM
    [CUBE];


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "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]

    • Marked as answer by GURSETHI Monday, May 21, 2018 4:27 AM
    Friday, May 18, 2018 7:35 AM

All replies

  • Hi,

    Try this query :

    WITH MEMBER [CurrentDate] AS ([Date].[Calendar].CurrentMember,[Measures].[Sale Amt])
    MEMBER [PreviousMonth] AS ([Date].[Calendar].PrevMember,[Measures].[Sale Amt])
    MEMBER [Changed] AS IIF([CurrentDate]<>[Changed],"Changed",'No change')
    
    SELECT 
    Tail(
    Descendants
    (
    [Date].[Calendar].CurrentMember, [Date].[Calendar].[Date]
    )
    ,5
    ) on 0,
    {[Measures].[Sale Amt],[Changed]} on 1
    FROM
    [CUBE];


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "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]

    • Marked as answer by GURSETHI Monday, May 21, 2018 4:27 AM
    Friday, May 18, 2018 7:35 AM
  • Hi GURSETHI,

    Thanks for your question.

    According to your description, all your logic is based on date level. Please try below MDX query:

    WITH MEMBER [Measures].[Changed] AS
    IIF(
         ([Date].[Calendar].CurrentMember,[Measures].[Sale Amt]) =
         ( [Date].[Calendar].CurrentMember.LAG(1),[Measures].[Sale Amt])
          AND 
         ([Date].[Calendar].CurrentMember,[Measures].[Sale Amt]) =
         ( [Date].[Calendar].CurrentMember.LAG(2),[Measures].[Sale Amt])
           AND 
         ([Date].[Calendar].CurrentMember,[Measures].[Sale Amt]) =
         ( [Date].[Calendar].CurrentMember.LAG(3),[Measures].[Sale Amt])
            AND 
         ([Date].[Calendar].CurrentMember,[Measures].[Sale Amt]) =
         ( [Date].[Calendar].CurrentMember.LAG(4),[Measures].[Sale Amt]),
         "NO Changed",'Changed'
    
    SELECT [Date].[Calendar].[Date] on 0,
    {[Measures].[Sale Amt],[Measures].[Changed]} on 1
    FROM
    [CUBE];


    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

    Monday, May 21, 2018 1:53 AM
    Moderator