If Value is Less than another value

[enswitzer]

I created a table that looks like this:

Decision Point	Target Date	Actual Date
A	11/1/2019	2/3/2019
B	2/4/2019	4/25/2019
C	4/15/2020	4/27/2020
D

I would like to create a column with a traffic light indicator that shows if the Decision Point is on plan. However, based on what I had to do to create the table, I'm stuck as to how to do that. The data looks like this, because I had to unpivot it to get it to show in the table I wanted to:

Is there an easy way to create the "if Actual Date for Decision Point A is > Target Date for Decision Point A then Red" logic that I want to create?

[v-zhenbw-msft · Accepted solution]

Hi @enswitzer ,

We can create three measures to meet your requirement.

1. Create actual date and target date measures.

Actual date = CALCULATE(MAX('Table'[value]),'Table'[Target vs Actual]="Actual")

Target date = CALCULATE(MAX('Table'[value]),'Table'[Target vs Actual]="Target")

2. Then we can create an icon measure.

Measure = IF([Actual date]>[Target date],1,0)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[enswitzer]

By the way - this WORKED PERFECT, thanks so much! (Also I wish I had asked this question sooner)

[enswitzer]

This is the logic I've been trying to figure out how to do! Thanks so much, I will play with this!

[Fowmy]

@enswitzer 

You can download the file: HERE

Flag = 
IF( HASONEVALUE('Table'[Decision Point]),
    IF( SELECTEDVALUE('Table'[Actual Date]) > SELECTEDVALUE('Table'[Target Date] ) , "🔴" , "🟢" )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[enswitzer]

I couldn't get this to work because I don't have a column that is "Target Date" or "Actual Date", I have a column that is "Target or Actual". Is there a way for me to extract those?

Also, I couldn't open the PBIX file because it was the wrong version and I can't download a new one without IT support sorry!

[Fowmy]

@enswitzer 

You can pivot these columns in power query 

https://youtu.be/IULqUeYEnto

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn