Data categorization and labeling based on date range

[Anonymous]

Hi - I have two tables Sales and Warranty as below. I want to add a calculated column to the sales table that looksup the Warranty table, matches the Product and if the ship date is between the Ship Start and Ship End from warranty table, adds the text thats in the Warranty column of the Warranty table. 

so something like =>  if(Sales Table[Product]=Warranty Table[Product] && Sales Table[ShipDate]>= Warranty Table[Ship Start] && ales Table[ShipDate]<= Warranty Table[Ship End], Warranty, blank())

How do I do this? I should be able to do this but I cant access another table when I am in the Sales Table.I do have ProductDim Table and ShipDateDim Tables that create a relationship between these tables. Ultimately What I want to do is, for the selected product, show a histogram of Sales qty over a period of time and just highlight the time periods that have Warranty issues.  I think if I am able to create the columns with category then I can use it as a legend in the chart. 

I would appreciate your advice. Thanks. 

[parry2k · Accepted solution]

Add following measure in your sales table:

Warranty = 
calculate(firstnonblank(Warranty[Warranty],1), 
filter(Warranty, 
Warranty[Product] =MAX( Sales[Product] ) && 
MAX(Sales[Date]) >= Warranty[Start Date] && 
MAX(Sales[Date]) <= Warranty[End Date]
)
)

[v-haibl-msft · Accepted solution]

@Anonymous

You can also use following DAX foumula to create a column.

Column = 
CALCULATE (
    MAX ( Warranty[Warranty] ),
    FILTER (
        Warranty,
        Sales[ShipDate] >= Warranty[Ship Start]
            && Sales[ShipDate] <= Warranty[Ship End]
            && Sales[Product] = Warranty[Product]
    )
)

Best Regards,
Herbert

[parry2k]

what happens if a product have record with ship start/end date in existing date range of the same product, or it will never happen

[Anonymous]

Good question. I was going to create a second column to deal with that. It will be rare but still a possibility. 

[parry2k · Accepted solution]

Add following measure in your sales table:

Warranty = 
calculate(firstnonblank(Warranty[Warranty],1), 
filter(Warranty, 
Warranty[Product] =MAX( Sales[Product] ) && 
MAX(Sales[Date]) >= Warranty[Start Date] && 
MAX(Sales[Date]) <= Warranty[End Date]
)
)