Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)))
Why did you include, Round(), and further, when I applied the formula to my chart, it came out as follows as all red, with the green bar as grey.
Never mind, I fixed it as follows:
=
IF(
Sum({$<[Metric]={'Direct Revenue'}, DATE={">=$(=today(DATE)) <=$(=Max(DATE))"}>}[Act/Fcst]), rgb(128, 128, 128),
IF(
(Sum({$<[Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Act/Fcst]) -
Sum({1<[Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Plan])) < 0,rgb(219,60,36),
IF(
(Sum({$<[Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Act/Fcst]) -
Sum({1<[Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Plan])) >= 0,Green())))