topic Re: Background Cell Color Highlight need reducing by subsequent Month in Developer

Background Cell Color Highlight need reducing by subsequent Month

shishir999 — Fri, 30 Nov 2018 06:02:54 GMT

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

Re: Background Cell Color Highlight need reducing by subsequent Month

shishir999 — Tue, 04 Dec 2018 06:14:54 GMT

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