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 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. 

 

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

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

    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'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
        Super 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]
        )
        )
  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    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