Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Data categorization and labeling based on date range

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 ...
Need Help
  • parry2k's avatar
    parry2k
    8 years ago

    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's avatar
    v-haibl-msft
    8 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

parry2k's avatar
parry2k
Icon for Super User rankSuper User
8 years ago

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