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

[Jabbajuice]

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: 

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'

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? 

[v-xiaotang · Accepted solution]

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

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.