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

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
Community Support

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.

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

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.

Helper III

Hi @Icey

Thank you for the reply,

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

Community Support

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.

Helper III

Hi @Icey

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

Community Support

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.

Helper III

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.

Community Support

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.

Helper III

Hi @Icey

Yes, this is working.

Thank you for your time.

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

Helper III

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.

Super User

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

Helper III

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors