Forum Discussion

shishir999's avatar
shishir999
Helper II
7 years ago

Background Cell Color Highlight need reducing by subsequent Month

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

 

Need Help

1 Reply

  • shishir999's avatar
    shishir999
    Helper II
    7 years ago

    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