Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
shishir999
Helper II
Helper II

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:

 

ds_Sample-01.JPGResult_Sample-02.JPG

 

 

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

 

1 REPLY 1
shishir999
Helper II
Helper II

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.