Forum Discussion
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...
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.
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.
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.
