Forum Discussion

Joe_84's avatar
Joe_84
Regular Visitor
3 years ago

Conditional formatting - With a relative date filter

Hello Folks

 

So I have a smart storage unit, and I have extracted some data to see my actual stock, vs the outbound movements of the last 4 and 12 weeks.

I would like to basically set a red background if the value in column 3 (stock) is lower than the value in column 2 (movements).

 

 

As you can see, I have two tables, one filtered by last 12 weeks and one by the last 4 weeks.

The data is working very good, but its visually not that easy to spot.

 

I had considered using a third column, that would be Column 3 - Column 2, but since the column 2 is variable because of the relative date filter, I could not make it work.

 

Any help would be appreciated.

 

Regards

 

Joe

 

PS relation between tables:

 

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Joe_84 ,

    It seems that what you are trying to create is a calculated column. While calculated column will not change according to the user interaction(slicer, filter, column selections etc.) in the report as the value of a calculated column is computed during data refresh and uses the current row as a context... Please review the following links about the difference of calculated column and measure...

    Calculated Columns and Measures in DAX

    Calculated Columns vs Measures

    You can follow the steps below to get it:

    1. Create a measure as below

     

    Conditional formatting = IF ( SUM ( 'Tablename'[Stock] ) < SUM ( 'Tablename'[Cant Mov] ), "Red" )

     

    2. Configure the conditional formatting for the value field [Stock] as below screenshot

    If the above one can't help you, please provide some raw data in your tables with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

     

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    • Joe_84's avatar
      Joe_84
      Regular Visitor
      3 years ago

      Hello Anonymous Thank you so much for your feedback.

       

      I will look in to providing raw data or a PBI.

       

      Currently I tried the "conditional formatting" measurement. This did not work

      When in conditional formatting, I dont have the option to select the measurement, as in your screenshot.

       

       

      There is an additional twist to this.

      "Cant Move" is not a column, its a COUNT of how many times that Ref appears in the movements table (how many times this product has been moved)

       

       

      EDIT:

       

      I think Maybe I need a measurement something like this.

       

      CondFormat = IF(SUM(GetBestand[Cantidad])<SUM(Countx(Filter( xxxxx )));"Red")

       

      Way beyond my current knowledge.