Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MintuBaruah
Helper III
Helper III

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.

1 ACCEPTED SOLUTION

Hi @MintuBaruah ,

 

Something like so?

Icey_0-1631870557849.png

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.

View solution in original post

12 REPLIES 12
Icey
Community Support
Community Support

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

 

Icey_1-1631848225891.png

 

 

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

 

Icey_0-1631848184986.png

 

 

 

Best Regards,

Icey

 

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

Hi @Icey 

 

Thank you for the reply,

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

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.

Hi @Icey 

 

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

 

to share.JPG

 

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.

Hi @Icey 

 

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

After making the above changes I am getting this:

to share.JPG

Thank you.

Hi @MintuBaruah ,

 

Something like so?

Icey_0-1631870557849.png

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.

Hi @Icey 

 

Yes, this is working.

Thank you for your time.

amitchandak
Super User
Super User

@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())

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.

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

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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors