Measure issue

[Tara_01]

Hi All,

I have a matrix where rows are different cost types, the column has months from January to December, the values is the amount which is of two types either forecast or invoiced. Filters applied are for "Items" & "Year".

If the amount is invoiced it should be shown green in the matrix for that month and if the amount is forecast it should be shown as blue in the matrix. Now I am facing the issue where in for the items have been invoiced but there is $0.00 occuring in that cell. Invoiced items don't have $0.00 but forecast have now for the previous years like 2020,2021,2022 which is entirely invoiced but some of the items have $0.00 which falls under forecast. I am able to acheive green colour for the previous years for all the values by below measure

If(OR(selectedvalue(itemtype)="Invoiced",c.amount = 0),"Light Green", "Sky Blue")

The issue with the above measure is for year 2023 & 2024 all of it should be blue since it is forecast but $0.00 in 2023 & 2024 is green because of the above measure which is wrong. I want everything to be blue for the 2023 & 2024. Can someone help me modify my measure. It would helpful.  

[Mahesh0016]

@Tara_01 Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Tara_01]

Hi,

Can you help me how to share the data I am not getting any options here to share the sample data and powerbi

[Mahesh0016]

@Tara_01 Good Morning,

If this post helps, please consider accept as solution to help other members find it more quickly.

[Tara_01]

Hi Mahesh,

Thankyou for your help but this is also turn the 0 in 2022 sky blue which I dont want. I want 0 in 2022  as green since it is invoiced. 

[Mahesh0016]

@Tara_01 

**If this post helps, please consider accept as solution to help other members find it more quickly.

[Tara_01]

Hi Mahesh,

Thanks again but I understand in my data some of the item types are blank because of which is it still considering 0 as green for the future years like 2023 & 2024. I tried both your solutions they din't work can you help me with a formula where if the item is blank and year is greater than 2023 it should be blue.

Thanks in advance 🙂