Pattern in table

[MintuBaruah]

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.

[Icey]

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]

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]

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]

Hi @Icey 

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

[Icey]

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]

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.

[Icey · Accepted solution]

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]

Hi @Icey 

Yes, this is working.

Thank you for your time.

[amitchandak]

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

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]

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

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.