Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Excellent! thank you!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |