Conditional Formatting

[vasubabu]

Hi,

Seems like the formatting is not working as expected and need your inputs.

Sample Data format for last months.

Based on the above data, I wanted to display sales per store for the last N months and highlight with a color if there is a drop/increase in the qty for each mont based on ..

If current-previous month >0 then "Green" color Ex: 4092 > 1506

If  current-previous month <0 then "Red" color Ex: 1623 < 4092

IF current-previous month =0 then "Black" color Ex: 999=999(not in data)

Created the dashboard for the last 6 months and applied conditonal formating.

However When I adjust the slicer value—for example, to “Last 5 months”—the formatting no longer applied  as intended. Expected 3010 as red color but showing as green.

Expected Output:

When I choose last 4 months the output shown as : but expected output is 1506 and 4067 numbers should be in red color.

Output:

Link for data and pbix file:

https://www.dropbox.com/scl/fo/x78r3g299b9v3r0dkxnxf/ALzapb6qt_XQNbwgxKqLYHM?rlkey=zu6sqsjgy212p0gx7...

[v-sgandrathi]

Hi @vasubabu,

I wanted to check if you had the opportunity to review the information provided by super users @alish_b @Ashish_Mathur @Irwan  and @danextian. Please feel free to contact us if you have any further questions

Thank you and continue using Microsoft Fabric Community Forum..

[vasubabu]

@danextian , @Ashish_Mathur , @Irwan  Updated the inital post as per the sample dataset and also attached the pbix file. Pls check.

[Ashish_Mathur]

Hi,

If you drag the previous month measure to your visual, you will see that there is no figure for 03/2025 (because you have chosen to show only the pat 5 months).  Since 3010 > 0, it appears in green.  That seems OK.

[vasubabu]

Hi @Ashish_Mathur  Technically that is correct but my usecase is to consider immediate previous month(if available in db) though the month is not part of date slicer or not displaying in the chart. Please let me know if there is any work around.

If this is not possible, then can u let me know in displaying the last N months(lowest month) only in black color rather than green color.. As 3010>0.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[alish_b]

Hi @vasubabu ,
I tweaked the Previous Month qty measure as follows:

Previous Month qty = 
CALCULATE([total_qty], PREVIOUSMONTH(DateTable[Date]), ALL(DateTable))

Basically, this is altering the filter context of date that is applied to the measure. 
Also, I changed the Date Slicer to have DateTable[Date] in the Field section instead of the order_date.

Also, one more important thing, I found a weird behavior in the relative date slicer in the report file (I wonder if it was just on my machine and won't be replicated on yours) that it wouldn't slice properly when I added a simple table with just DateTable[Date] as Columns. It also caused the above solution to not behave as expected. (It took me for a wild goose chase and I started questioning the universe) Anyway, I deleted the slicer and added a new one and it worked as expected.

Hope it helps!

[danextian]

Hi @vasubabu 

The relative date slicer is based on the current system date, not the last date in your semantic model. Also, I noticed you're using my usual sample file  - I'm curious what changes you made, since your conditional formatting isn't working. Did you make sure the Dates table is has been marked as such? Also, when debugging it is best to show the measures involve and not just the measure the conditional formatting is applied to so you or people will know which one could be causing the problem.

[Ashish_Mathur]

Hi,

Please explain your requirement in simple langage.  Share the download link of the PBI file.  Show the expected result there.

[Irwan]

hello @vasubabu 

i am not sure how your data looks like but it seems to me the conditional formating only for values.

There is no conditional formating for date therefor changing date slicer will not have any effect on conditional formating.

otherwise, please share your sample data that represent your original data (remove any confidential information) including with your expected/desired result.

you can upload your pbix in online storage like dropbox, etc.

Thank you.