Forum Discussion

DJ1977's avatar
DJ1977
Icon for Helper I rankHelper I
5 years ago
Solved

Add column for campaign within daterange

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

    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.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

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's avatar
    DJ1977
    Icon for Helper I rankHelper I
    5 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        DJ1977
        Icon for Helper I rankHelper I
        5 years ago

        Hi Anonymous  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