Add column for campaign within daterange

[DJ1977]

Hello. I have a small case here that I can not find a solution to by searching the forum here. Hope someone can give me a tip.
I have a model with two tables, A & B, among several.

These two are not related. I want to add a column in Table B with Campaign from Table A. It should add campaign when hits on product, customer and sales date hits in validity within campaign date range.
Is there a DAX formula that can solve this?

[v-shex-msft]

HI @DJ1977,

It sounds like a general multiple date range analysis requirements, you can take a look at the following link 'start date', 'end date' part of it suitable for your sceniaro.

Before You Post, Read This 

Regards,

Xiaoxin Sheng

[amitchandak]

@DJ1977 , Try this formula. As a new Dax column

minx(filter(TableA, TableA[StartDate] <=Tableb[SalesDate] && TableA[endDate] >=Tableb[SalesDate] && && TableA[productID] >=Tableb[productID]), TableA[]campaign])

Refer this video , how copy value from one table to another :https://www.youtube.com/watch?v=czNHt7UXIe8

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=CALCULATE(VALUES(Table1[Campaign]),FILTER(Table1,Table1[Customer]=EARLIER(Table2[Customer])&&Table1[Product ID]=EARLIER(Table2[Product ID])&&Table1[Start date]<=EARLIER(Table2[Salesdate])&&Table1[End date]>=EARLIER(Table2[Salesdate])))

Hope this helps.

[DJ1977]

Hi @Ashish_Mathur , and thank you very much for your answer. I tryed this solution, but i got an error telling me "DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

Both Campaign and Customer are textcolumns. Is there a way i can go around this error?

Regards

DJ

[v-shex-msft]

HI @DJ1977,

It sounds like a common issue when you use the 'math' operators to compare with different types of values. Please check your two table date fields to confirm if they are correctly formatted as date types.

Regards,

Xiaoxin Sheng

[DJ1977]

Hi @v-shex-msft  and thank you very much for your answer. This fixed the error and now i have a solution to my problem, with help of your tip and @Ashish_Mathur DAX formula above.

Regards

DJ

[v-shex-msft]

HI @DJ1977,

I'm glad to hear that my suggestion helps to solve the issue.🙂

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

You are welcome.