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.