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   NO...
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.

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.

  • MintuBaruah's avatar
    MintuBaruah
    Helper III
    4 years ago

    Hi Icey 

     

    This is showing for both the first and the changed value as True().

     

     

    • Icey's avatar
      Icey
      Community Support
      4 years ago

      Hi MintuBaruah ,

       

      Do you mean that you want the first one to show "False"? If so, add another judgment condition:

      Column=

      xxxxx

      RETURN
      IF (
      'Table'[Debit] <> BLANK (),

       IF (
                  PreviousDebit_ = BLANK (),
                  "False",
      IF ( 'Table'[Debit] <> PreviousDebit_, "True", "False" )
      ))

       

       

      Measure= 
      xxxxx

      RETURN
          IF (
              MAX ( 'Table'[Debit] ) <> BLANK (),
              IF (
                  PreviousDebit_ = BLANK (),
                  "False",
                  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 

         

        Yes, I only want the Flag (True()) for the changed value(ie 200000.).

        After making the above changes I am getting this:

        Thank you.