Forum Discussion
Solved
Calaculate the difference between one column if the other column contains a certain text
Hi There. I was hoping you could help me with this. I have a table where I would like the difference to be calculated based on the item number So for example Item Number 22 I'd like the d...
HI MaighreadL ,
Create a Calculated Column
Difference = VAR a = CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( 'Table', 'Table'[Item Number] = EARLIER ( 'Table'[Item Number] ) && 'Table'[Date] < EARLIER ( 'Table'[Date] ) ) ) VAR b = CALCULATE ( MAX ( 'Table'[Unit Cost] ), FILTER ( 'Table', 'Table'[Item Number] = EARLIER ( 'Table'[Item Number] ) && 'Table'[Date] = a ) ) RETURN IF ( b <> BLANK (), 'Table'[Unit Cost] - b )Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
HI MaighreadL ,
Create a Calculated Column
Difference =
VAR a =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
'Table',
'Table'[Item Number]
= EARLIER ( 'Table'[Item Number] )
&& 'Table'[Date]
< EARLIER ( 'Table'[Date] )
)
)
VAR b =
CALCULATE (
MAX ( 'Table'[Unit Cost] ),
FILTER (
'Table',
'Table'[Item Number]
= EARLIER ( 'Table'[Item Number] )
&& 'Table'[Date] = a
)
)
RETURN
IF (
b
<> BLANK (),
'Table'[Unit Cost] - b
)
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!
Anonymous This is perfect !!! Thank you so much !!!