Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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.
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.
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:
Thank you.
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 , 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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
105 | |
98 | |
39 | |
30 |