cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SurfnSoCal
New Member

Conditional Formatting Table

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).

 

Untitled.png

 

thanks in advance!

1 ACCEPTED SOLUTION
sevenhills
Super User
Super User

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

 

Data

sevenhills_0-1642817486830.png

 

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

 

sevenhills_3-1642817923799.png

 

 

sevenhills_2-1642817891624.png

 

 

output

sevenhills_1-1642817829430.png

 

 

View solution in original post

2 REPLIES 2
SurfnSoCal
New Member

Excellent!  thank you!

sevenhills
Super User
Super User

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

 

Data

sevenhills_0-1642817486830.png

 

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

 

sevenhills_3-1642817923799.png

 

 

sevenhills_2-1642817891624.png

 

 

output

sevenhills_1-1642817829430.png

 

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!