Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SDolan1999
New Member

Need help with DAX formula for date conditional formatting

Hello! I need help writing a DAX formula that will allow me to use conditional formatting in a matrix to visually show when a rep was added to an account. 

 

This is a matrix visual, and I'd like the formatting to roughly appear like the below:

SDolan1999_0-1670013372916.png

This is the DAX formula I wrote:

Format for Start Date =
//Add indicator for start date in the Sales by Rep and Account Table
var PerStart = MAX('Specialist'[Period Sequence Added to Account])
var PerCode = MAX('Dim-FiscalCalendar'[FSC_PRD_SEQ_NUM])
RETURN
IF (PerStart = PerCode, 1, 0)
 
And set up that the cells should be green if Format for Start Date = 1
 
I wrapped the to measures in MAX because, without that, I get this message: 
A single value for column 'Period Sequence Added to Account' in table 'Specialist' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
 

I should have added that the 'Specialist' table contains several columns, but the two specific to the solution are: a Date column which is date format, and the column with the Period Sequence Number merged into the table in Power Query Editor.

 

The second table, 'Dim-Fiscal Calendar' is our fiscal calendar table that contains dates, and includes the Period Sequence Number in column FSC_PRD_SEQ_NUM. 

 

These tables are joined 'Dim-Fiscal Calendar' 1:* 'Specialist'.

1 REPLY 1
lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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