Cumulative and Rangesum() Color Conditioning

[#1]

Using data that is reported on a month to month basis, I am trying to color condition the data based on the following:

=
IF(
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=today(DATE)) <=$(=Max(DATE))"}>}[Sales Data.Act/Fcst]), vColorFORECAST,

IF(
(Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Sales Data.Act/Fcst])
-
Sum({1<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Sales Data.Plan])) < 0, vColorACTUALRED,

IF(
(Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Sales Data.Act/Fcst])
-
Sum({1<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=Min(DATE)) <$(=today(DATE))"}>}[Sales Data.Plan])) >= 0, vColorACTUALGREEN)))

However, my color conditions are only comparing each months Act/Fcst less Plan, as opposed to the cumulative value up to the date in question. I have since tried the following, but have yet to format the colors correctly. any and all help is much appreciated. Best.

=
IF(
Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=today(DATE)) <=$(=Max(DATE))"}>}[Sales Data.Act/Fcst]), vColorFORECAST,

IF(
Rangesum(Above(Sum({$<[Sales Data.Metric]={'Direct Revenue'}>}[Sales Data.Act/Fcst]), 0, RowNo()))
-
Rangesum(Above(Sum({1<[Sales Data.Metric]={'Direct Revenue'}>}[Sales Data.Plan])), 0, RowNo()) < 0, vColorACTUALRED,

IF(
Rangesum(Above(Sum({$<[Sales Data.Metric]={'Direct Revenue'}>}[Sales Data.Act/Fcst]), 0, RowNo()))
-
Rangesum(Above(Sum({1<[Sales Data.Metric]={'Direct Revenue'}>}[Sales Data.Plan])), 0, RowNo()) >= 0, vColorACTUALGREEN)))

[sinanozdemir]

Hi Michael,

Maybe use RowNo(TOTAL)?

[#3]

Hi,

Please try Above only in instead of Rangesum, and use the label of the field that you have given for the expression  in the chart :

Like if expression Label is "Label"  then your expression for cumulative sum will be like.

= Above([Label]) +  Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=today(DATE)) <=$(=Max(DATE))"}>}[Sales Data.Act/Fcst])

Hope This Helps.

Regards,

Vivek

[#4]

Great, but how do you account for the first value? Using the following formula I was able to color condition my data, but am missing the first value.

Above(Sum({$<[Sales Data.Metric]={'Direct Revenue'}, DATE={">=$(=today(DATE)) <=$(=Max(DATE))"}>}[Sales Data.Act/Fcst]))

[#5]

Hi Michael,

You can use something like this :

if(isnull(Above([Label])), 0, Above([Label]) + "Your Expression ")

Regards,

Vivek