cancel
Showing results 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().

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.

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