Forum Discussion

MintuBaruah's avatar
MintuBaruah
Helper III
4 years ago
Solved

Pattern in table

Hi @all

 

Narrative Ref. Date Post Date Debit
Global SALARY 4/24/2021 2/24/2021 200000.00
HLDGS NONREF 4/18/2021 3/16/2021  
HLDGS NONREF 3/25/2021 3/16/2021  
Global SALARY 3/24/2021 2/24/2021 10000.00
Limited NONREF 3/20/2021 3/16/2021  
Limited NONREF 3/19/2021 3/16/2021  
Global SALARY 2/24/2021 2/24/2021 10000.00
HLDGS REFUND 12/23/2020 12/23/2020  
Limited INTEREST 12/23/2020 12/23/2020  

 

I have a data set similar to this, 

In this example, Global Narrative is repeating every month and the ref. is Salary. I need to identify if the amount changes from the previous one(ie. from 10000.00 to 200000.00). If so, then it should flag it as True().

 

Please help resolve this issue.

Thank you.

Need Help
  • Icey's avatar
    Icey
    4 years ago

    Hi MintuBaruah ,

     

    Something like so?

    Column = 
VAR PreviousDate_ =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Narrative] = EARLIER ( 'Table'[Narrative] )
                && 'Table'[Ref.] = EARLIER ( 'Table'[Ref.] )
                && 'Table'[Date] < EARLIER ( 'Table'[Date] )
        )
    )
VAR PreviousDebit_ =
    CALCULATE (
        MAX ( 'Table'[Debit] ),
        FILTER (
            'Table',
            'Table'[Narrative] = EARLIER ( 'Table'[Narrative] )
                && 'Table'[Ref.] = EARLIER ( 'Table'[Ref.] )
                && 'Table'[Date] = PreviousDate_
        )
    )
RETURN
    IF (
        'Table'[Debit] <> BLANK ()
            && PreviousDebit_ <> BLANK (),
        IF ( 'Table'[Debit] <> PreviousDebit_, "True" )
    )

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

12 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    MintuBaruah , Try a new column like

     


    new column =
    var _1 = Maxx(filter(Table, [Narrative] = earlier([Narrative]) && [Ref.] = earlier([Ref.]) && [Narrative] ="Global" && [Ref.] = "Salary" && [Date] < earlier([Date])),[Date])
    return
    if( [Narrative] ="Global" && [Ref.] = "Salary" , Maxx(filter(Table, [Narrative] = earlier([Narrative]) && [Ref.] = earlier([Ref.]) && [Narrative] ="Global" && [Ref.] = "Salary" && [Date] =_max ),[Debit]), blank())

    • MintuBaruah's avatar
      MintuBaruah
      Helper III
      4 years ago

      Hi amitchandak 

       

      Thank you for the reply,

      but it should be dynamic values(ie Global and Salary cant be used in the dax.).

      This was just an example.

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        MintuBaruah , Try like, this will work for the same set

         

        new column =
        var _1 = Maxx(filter(Table, [Narrative] = earlier([Narrative]) && [Ref.] = earlier([Ref.]) && [Date] < earlier([Date])),[Date])
        return
         Maxx(filter(Table, [Narrative] = earlier([Narrative]) && [Ref.] = earlier([Ref.]) && [Narrative] ="Global" && [Ref.] = "Salary" && [Date] =_max ),[Debit])

  • Icey's avatar
    Icey
    Community Support
    4 years ago

    Hi MintuBaruah ,

     

    Try this:

     

    Column = 
VAR PreviousDate_ =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Narrative] = EARLIER ( 'Table'[Narrative] )
                && 'Table'[Ref.] = EARLIER ( 'Table'[Ref.] )
                && 'Table'[Date] < EARLIER ( 'Table'[Date] )
        )
    )
VAR PreviousDebit_ =
    CALCULATE (
        MAX ( 'Table'[Debit] ),
        FILTER (
            'Table',
            'Table'[Narrative] = EARLIER ( 'Table'[Narrative] )
                && 'Table'[Ref.] = EARLIER ( 'Table'[Ref.] )
                && 'Table'[Date] = PreviousDate_
        )
    )
RETURN
    IF (
        'Table'[Debit] <> BLANK (),
        IF ( 'Table'[Debit] = PreviousDebit_, "True", "False" )
    )

     

     

     

    Or this:

     

    Measure = 
VAR PreviousDate_ =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Narrative] = MAX ( 'Table'[Narrative] )
                && 'Table'[Ref.] = MAX ( 'Table'[Ref.] )
                && 'Table'[Date] < MAX ( 'Table'[Date] )
        )
    )
VAR PreviousDebit_ =
    CALCULATE (
        MAX ( 'Table'[Debit] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Narrative] = MAX ( 'Table'[Narrative] )
                && 'Table'[Ref.] = MAX ( 'Table'[Ref.] )
                && 'Table'[Date] = PreviousDate_
        )
    )
RETURN
    IF (
        MAX ( 'Table'[Debit] ) <> BLANK (),
        IF ( MAX ( 'Table'[Debit] ) = PreviousDebit_, "True", "False" )
    )

     

     

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • MintuBaruah's avatar
      MintuBaruah
      Helper III
      4 years ago

      Hi Icey 

       

      Thank you for the reply,

      this is almost correct but the Flag (True) should be on 200000.00 instead of 10000.00.

      • Icey's avatar
        Icey
        Community Support
        4 years ago

        Hi MintuBaruah ,

         

        Sorry, a little mistake. Just change the expressions like so:

        Column = 
VAR PreviousDate_ =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Narrative] = EARLIER ( 'Table'[Narrative] )
                && 'Table'[Ref.] = EARLIER ( 'Table'[Ref.] )
                && 'Table'[Date] < EARLIER ( 'Table'[Date] )
        )
    )
VAR PreviousDebit_ =
    CALCULATE (
        MAX ( 'Table'[Debit] ),
        FILTER (
            'Table',
            'Table'[Narrative] = EARLIER ( 'Table'[Narrative] )
                && 'Table'[Ref.] = EARLIER ( 'Table'[Ref.] )
                && 'Table'[Date] = PreviousDate_
        )
    )
RETURN
    IF (
        'Table'[Debit] <> BLANK (),
        IF ( 'Table'[Debit] <> PreviousDebit_, "True", "False" ) --------------changed
    )
        Measure = 
VAR PreviousDate_ =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Narrative] = MAX ( 'Table'[Narrative] )
                && 'Table'[Ref.] = MAX ( 'Table'[Ref.] )
                && 'Table'[Date] < MAX ( 'Table'[Date] )
        )
    )
VAR PreviousDebit_ =
    CALCULATE (
        MAX ( 'Table'[Debit] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Narrative] = MAX ( 'Table'[Narrative] )
                && 'Table'[Ref.] = MAX ( 'Table'[Ref.] )
                && 'Table'[Date] = PreviousDate_
        )
    )
RETURN
    IF (
        MAX ( 'Table'[Debit] ) <> BLANK (),
        IF ( MAX ( 'Table'[Debit] ) <> PreviousDebit_, "True", "False" )       -------------changed
    )

         

         

        Best Regards,

        Icey

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.