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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MsWhyte
Frequent Visitor

Measure for conditional formatting

I have a matrix table with the following:

Product in rows, Region in columns, Values are the total amount of items we have on stock, and the tooltip shows the location of the product, the specific lot and the lot's expiration date. All records come from table "Inv Producto"

 

I want the matrix to have conditional format to indicate what product of what region has expiration dates in X days (using a What If parameter, I think that's where the issue resides in) so technically the '3597' should be highligted in Red just like the other cells of the table, and when you hover over the value, you can identify which lot is the one about to expire...but it is not being highlighted and those items could be lost in the analysis.

 

MsWhyte_0-1641930635528.png

Measure for conditional formatting is: 

Below Threshold color = IF ( AVERAGEA('Inv Producto'[DaysToExpiry]) <= DiasAntesDeExpirar[DaysToExpire Value], "Coral" )
 
Column DaysToExpiry is:
DaysToExpiry=Duration.Days(Date.From([ExpDate])-Date.From(DateTime.LocalNow())). ExpDate is in mm/dd/yy format
 
What If Parameter to define expiration in the next X days:
DiasAntesDeExpirar = GENERATESERIES(0, 365, 1)
 
I think the issue is that CDMX is the one that has most products, so the average I am calculating is very high, thus it doesn't meet the criteria for the formatting but I have no idea how to do the calculation to identify each lot's expiration date and format based on that value only. 
 
Any help is appreciated!
3 REPLIES 3
amitchandak
Super User
Super User

@MsWhyte , Not very clear

 

a color measure like below, this can be used in conditional formatting using filed value option

if( max('Inv Producto'[ExpDate])<= today() + selectedvalues(DiasAntesDeExpirar[Value])  && max('Inv Producto'[ExpDate])>= today() , "Red", "Green")

Sorry if I was not clear. My color formatting does what it is supossed to do, but I made a mistake calculating the expiration dates, I tried with SUM and AVERAGE but that number is not what I want. Example if I have 500 products that expire next week, then 500 that expire in 3 months, then the value will not be highlighted because the AVG time is a month and a half and that is already higher than the 7 day value I want to look for.

 

How could I make a measure to achieve that?

Hi @MsWhyte ,

I am not clear about your data model and the logic of calculation. Please refer to this blog to describe your problem:

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 


Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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