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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Jabbajuice
Frequent Visitor

DAX Calculated Column- comparing date column based on filtering from two other columns

I'm not sure how to word the issue I'm having, but I'll try. 

 

So basically I have Hardware name, start date, finish date, and review type as column names for scheduling data. There can be multiple Hardware names that correspond to different type of reviews (For example hardware name 'pipes' goes through a cycle of different reviews in a specific order- Step 1. design review, Step 2. critical review, Step 3. product build....etc. so 'pipes will have multiple columns, similar to this example: 

 

Jabbajuice_0-1654121775919.png

 

I'm making a gantt chart, where I'm using 'Hardware Names' as my rows, and grouping tasks together so you can view all the 'Hardware's' reviews from one end to the other, therefore viewing the entire review lifecycle of that hardware unit. 

 

However, I'm also bringing in columns where the review type IS NULL, but the Hardware name is present. There are gaps within the review lifecycle that aren't accounted for in the 'review type' column that go inbetween the other review types.

 

For example, I need to add in 'Test Product' into the review types for 'Pipes'. 'Test product' goes before 'Product Build' and after 'Critical Review'

Jabbajuice_1-1654122472424.png

 

Since I'm comparing start date to start date, filtered by hardware name and review type, how would I write this DAX calculated column correctly? 

 

 

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
Community Support

Hi @Jabbajuice 

Thanks for reaching out to us.

You can try this column 

Column = 
var _a="Test product"
var _cr=CALCULATE(MAX('Table'[Start Date]),FILTER( ALLEXCEPT('Table','Table'[Hardware Name]),'Table'[Review Type]="Critical Review"))
var _pb=CALCULATE(MAX('Table'[Start Date]),FILTER( ALLEXCEPT('Table','Table'[Hardware Name]),'Table'[Review Type]="Product Build"))
return IF('Table'[Start Date]>_cr && 'Table'[Start Date]<_pb,_a,'Table'[Review Type])

vxiaotang_0-1654495536452.png

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-xiaotang
Community Support
Community Support

Hi @Jabbajuice 

Thanks for reaching out to us.

You can try this column 

Column = 
var _a="Test product"
var _cr=CALCULATE(MAX('Table'[Start Date]),FILTER( ALLEXCEPT('Table','Table'[Hardware Name]),'Table'[Review Type]="Critical Review"))
var _pb=CALCULATE(MAX('Table'[Start Date]),FILTER( ALLEXCEPT('Table','Table'[Hardware Name]),'Table'[Review Type]="Product Build"))
return IF('Table'[Start Date]>_cr && 'Table'[Start Date]<_pb,_a,'Table'[Review Type])

vxiaotang_0-1654495536452.png

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.