Forum Discussion

MsWhyte's avatar
MsWhyte
Frequent Visitor
4 years ago

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.

 

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!
Expiration Dates
measure

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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")

    • MsWhyte's avatar
      MsWhyte
      Frequent Visitor
      4 years ago

      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?