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

[TLW_STL]

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

[parry2k · Accepted solution]

@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! ❤️

[TLW_STL]

It worked!  Thanks so much!

[parry2k]

@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! ❤️

[TLW_STL]

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?

[TLW_STL]

@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]

@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!

[parry2k]

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

[TLW_STL]

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]

@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

[TLW_STL]

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.