cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## How to apply conditional formatting on latest month data only in matrix

I have a measure called Alert, which assigns an X if the value of the current month exceeds a threshold.  Here are the three formulas:

Current Month = CALCULATE(SUM(Sales[Volume]),DATESMTD('Date Table'[Date]))

Current Month -1 = CALCULATE(SUM(Sales[Volume]),DATESMTD(DATEADD('Date Table'[Date],-1,MONTH)))

Current Month -2 = CALCULATE(SUM(Sales[Volume]),DATESMTD(DATEADD('Date Table'[Date],-2,MONTH)))

Current Month -3 = CALCULATE(SUM(Sales[Volume]),DATESMTD(DATEADD('Date Table'[Date],-3,MONTH)))

Avg 3 Prior Mos = (('Date Table'[Current Month -1])+('Date Table'[Current Month -2])+('Date Table'[Current Month -1]))/3

Threshold = ABS([Avg 3 Prior Mos])*1.5

Alert = IF([Current Month]>[Threshold],"X", " ")*1.5

The calculations work in Table 1 (matrix) and I used the Alert measure to create conditional formatting on the Current Month to display a red flag icon.

Table 1 Screenshot

However, when I try to apply the Alert measure in Table 2 (see below), the Alert measure does not work correctly.

Table 2 Screenshot

Is there a way to edit the Alert measure to work in Table 2?  Is there a way in Table 2 to sort the column headings to display left to right (i.e., most recent month at far left)?  I could not paste all of my source data, but I have a few rows pasted below.

Any suggestions would be greatly appreciated!

 Date Year Month Contract # Item Code Item Volume Price Total Source Agent BankName 6/1/2023 2023 6 1026 000230 PAX tool 12.00 4.00 \$48.00 Online Sam ABC & Co 6/1/2023 2023 6 1026 010000 DDM part 1.00 5.00 \$5.00 Phone Sam ABC & Co 6/1/2023 2023 6 1026 100010 Sorter 25.00 6.00 \$150.00 Online Sam ABC & Co 6/1/2023 2023 6 1026 100015 Diverter 10.00 29.00 \$290.00 Online Sam ABC & Co 6/1/2023 2023 6 1026 100154 SM bin 7.00 12.00 \$84.00 Online Sam ABC & Co 6/1/2023 2023 6 1026 100200 LG bin 8.00 17.00 \$136.00 Online Sam ABC & Co

1 ACCEPTED SOLUTION
Super User

@TLW_STL are you adding a measure or a column? It should be a column.

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

10 REPLIES 10
Super User

@TLW_STL are you adding a measure or a column? It should be a column.

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

It worked!  Thanks so much!

Super User

@TLW_STL solution attached, you have to work with the date table to make it work and little tweaks in the measure.

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

Hi parry2K.  Thanks for the suggestion.  However when I tried to creat the Month measure in the Date Table, I got this error.  Any ideas why?

Frequent Visitor

I tried setting up access on my home pc for OneDrive and uploaded my PBIX file.  Please let me know if it worked.   Thanks!

Super User

@TLW_STL not sure what else to tell. Maybe create a file with sample data and share it, if you cannot, not sure there is much I can do. I hope someone else can help. Good luck!

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@TLW_STL you can always share using one drive/google drive.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

Sorry that is not an option for me.  Security measures in place do not allow anyone outside the company to access files on our OneDrive site.

Super User

@TLW_STL couple of things, first make your date table as date, and 2nd in table 2, use the month column from the date table, not your transaction table.

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

Hello Parry2K.  Thank you for your advice, but it did not work for me.  Based on how my data is structured, how would I be able to apply my Alert measure, but only to the latest month in the data set?  Or do I need an entirely different calculation?

The red icon flag should display only for June for items that exceeded the threshold.

Sorry I can't provide more.  I wish I could upload my Pbix file to this forum.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors