Conditional Format Date Range

[#1]

I browsed the community and still am having trouble formatting values based on a given date range. For values that are less than the current month, I want to format them either red or green, while values greater than or equal to the current month colored grey. My current background color expression is as follows, any suggestions?

=
IF(
(Sum({$<[Sales Data.Metric]={'Direct Revenue'}, [FiscalMonth]={">=$(=Max(FiscalMonth)<=$(=Max(FiscalMonth)"}> [Sales Data.Actual])
-
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, [FiscalMonth]={">=$(=Max(FiscalMonth)<=$(=Max(FiscalMonth)"}>} [Sales Data.Plan])) < 0,
rgb(219,60,36),
IF(
(Sum({$<[Sales Data.Metric]={'Direct Revenue'}, [FiscalMonth]={">=$(=Max(FiscalMonth)<=$(=Max(FiscalMonth)"}> [Sales Data.Actual])
-
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, [FiscalMonth]={">=$(=Max(FiscalMonth)<=$(=Max(FiscalMonth)"}>} [Sales Data.Plan])) >= 0,
Green(),
rgb(128, 128, 128)))

[sunny_talwar]

Is your FiscalMonth a numeric field? or does it look like Jan, Feb..and so on?

[#3]

Jan, Feb, ...., does it need to be numeric?

[sunny_talwar]

Max(FiscalMonth) converts the format of Month into Numeric field. Do you have a numeric equivalent of FiscalMonth in your data??? Something like FiscalMonthNo

[#5]

Yes, DN = The numerical value of the date, ####

[sunny_talwar]

See if this helps:

=IF(
(Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DN={">=$(=MonthStart(Date(Max(DN)))) <=$(=MonthEnd(Date(Max(DN))))"}> [Sales Data.Actual])
-
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DN={">=$(=MonthStart(Date(Max(DN)))) <=$(=MonthEnd(Date(Max(DN))))"}>} [Sales Data.Plan])) < 0,
rgb(219,60,36),
IF(
(Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DN={">=$(=MonthStart(Date(Max(DN)))) <=$(=MonthEnd(Date(Max(DN))))"}>} [Sales Data.Actual])
-
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DN={">=$(=MonthStart(Date(Max(DN)))) <=$(=MonthEnd(Date(Max(DN))))"}>} [Sales Data.Plan])) >= 0,
Green(),
rgb(128, 128, 128)))

[jonathandienst]

This might help:

Why don’t my dates work?

[#8]

Still not working. I simply want to write out for the first f(x) if month is between MIN(DN) and Current(DN).

[sasiparupudi1]

{">=$(=Max(FiscalMonth)<=$(=Max(FiscalMonth)"}

Do you only want the data for max(FiscalMonth)?.. It looks wrong to me

[#10]

No, for the first and second IF() I want dates between JAN and Today, then for the third and fourth I want dates greater than or equal to Today.