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.

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])

      • MintuBaruah's avatar
        MintuBaruah
        Helper III
        4 years ago

        Hi amitchandak 

         

        This is not working.

        And In Return you have used Global and Salary. I have to take dynamic values as this can change in future.

        Is there a way to resolve this like this.