Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
TLW_STL
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
TLW_STL_1-1691077130470.png

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

 

Table 2 Screenshot

TLW_STL_2-1691077235989.png

 

 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!

DateYearMonthContract #Item CodeItemVolumePriceTotalSourceAgentBankName
6/1/2023202361026000230PAX tool12.004.00$48.00OnlineSamABC & Co
6/1/2023202361026010000DDM part1.005.00$5.00PhoneSamABC & Co
6/1/2023202361026100010Sorter25.006.00$150.00OnlineSamABC & Co
6/1/2023202361026100015Diverter10.0029.00$290.00OnlineSamABC & Co
6/1/2023202361026100154SM bin7.0012.00$84.00OnlineSamABC & Co
6/1/2023202361026100200LG bin8.0017.00$136.00OnlineSamABC & Co
 
1 ACCEPTED SOLUTION
parry2k
Super User
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.

View solution in original post

10 REPLIES 10
parry2k
Super User
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.

It worked!  Thanks so much!

parry2k
Super User
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.

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?

Screen shot 1.jpg

TLW_STL
Frequent Visitor

@parry2k 

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

 

https://1drv.ms/u/s!AlmbuYo-FkN-hzrQqDzW3WAr8vem?e=tAMulx

parry2k
Super User
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.

parry2k
Super User
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.

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.

parry2k
Super User
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.

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?

 

Screen shot 1.jpg

 

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

 

TLW_STL_0-1691517793946.png

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors