Background Cell Color Highlight need reducing by subsequent Month

[shishir999]

Hi,

I have a sample data and based on that, able to color the background of expected field for each month as see below:

The code, I used:

------------------------ 

IoP_Col =

Var qq = CALCULATE(SUM(Data[Amount]), 

                  FILTER(Data, IF(SEARCH("IOP*",Data[Version],1,0) || SEARCH("Test*",Data[Version],1,0) = 1, 1,0) ))

Var rr = IF(qq > 0 , 1, 0)

Var pp = CALCULATE(rr, FILTER(Data, IF(Data[Version] = "IOP1" && Data[FY_Mon] = 04, 1,0) ||

                                            IF(Data[Version] = "IOP2" && (Data[FY_Mon] = 05 || Data[FY_Mon] = 06), 1,0)            ))

Return  pp

-------------------------------- 

Based on this code, the conditional formatting is highlighting all the rows, but not removing the color for each subsequent month. Could someone help on it.

Thanks

SG

[shishir999]

Hi,

I was able to get the result, but need to write Nested-IF for each month.

Here is the code:

------------------------ 

Var pp = CALCULATE(rr, FILTER(Data,
     IF(Data[Version] = "IOP1" && Data[FY_Mon] = 04 &&

         SEARCH("IOP*",Data[Version],1,0) || SEARCH("Test*",Data[Version],1,0) = 1, 1,

    IF(Data[Version] <> "IOP1" && Data[FY_Mon] = 05 &&

         SEARCH("IOP*",Data[Version],1,0) || SEARCH("Test*",Data[Version],1,0) = 1, 1,

    IF(Data[Version] <> "IOP2" && Data[Version] <> "IOP1" && (Data[FY_Mon] = 06    &&

         SEARCH("IOP*",Data[Version],1,0) || SEARCH("Test*",Data[Version],1,0) = 1, 1, 0 )))  ..... so on for 12 months.

))

Do we have any better solution...?

Thanks

Shishir