Conditional Formatting Table

[Anonymous]

Hello!

Hoping to get some help here.

I have a data set with Customer Name, OrderID, Promise Date, Ship Date, and Order Amount. 

I'm grouping the data by Customer and Month (see below).  I would like to conditionally format those orders where the ship month (column) is either before or after compared to the (row) promise month (i.e. early, on time, late). If possible, I'm trying to ignore the Day and simply compare the month (i.e. Shipping Feb 3rd with a Promise date of Feb. 5th is the same month).

For example- 

We're shipping $1,350.02 in 2022-02 where the promise date was 2022-01, cell would be red (late). 

$98,145.04 is shipping 2022-02 and promised in 2022-01, cell would be white (on time).

$189,246.83 is shipping in 2022-02 and is promised in 2022-03, cell would be blue (shipping early).

thanks in advance!

[Anonymous]

Excellent!  thank you!

[sevenhills · Accepted solution]

Without data pasted or PBIX file, it is tough. But, your explanation is perfect! Tried with only four values...

Data

Measure

Font color = 

var _a = Max('Table'[Ship Date])
var _b = Max('Table'[Promise Date])
var _m = datediff(_a, _b, month)

return switch (
  true (),
  _m > 0, "blue",
  _m < 0 , "red",
  ""
)

Color setting

output