Tagging Start and End dates for Sales Campaign

[jhauw74]

Hi Fellow experts - I am trying to create a DAX that will indicate with 1 or 0 - when the sales campaign starts & end.

I have tried this DAX : 

Active_Campaign =

Var Start_C = LOOKUPVALUE(Active_Campaign[Campaign Start Period],Active_Campaign[Campaign Start Period],[Calendar_Date])

Var End_C = LOOKUPVALUE(Active_Campaign[Campaign End Period],Active_Campaign[Campaign End Period],[Calendar_Date])

Return

IF(AND(

    Start_C >= TODAY(),

    End_C) <= TODAY(),

    "1","0")

However it errors out : 
DAX comparison operations do not support comparing values of type True/False with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

Lookup value is for getting a date value from another table for both Start & End of the Sales Campaign.
Any ideas on  how to make this working? TIA.

[jhauw74]

Thanks - I have the 2 tables below - hope it will be clear:

Table 1 - Date look up - indicating when the campaign start and ends

Campaign Start Period	Campaign End Period
12-Oct-23	12-Dec-23

Table 2: this is where the DAX calculation will be added as a new column in my dim Calendar Table - where tagging dates during campaign period as 1, outside that period will be tagged as 0.

Desired Results:

Daily Campaign Dates	Tags
24-Sep-23	0
25-Sep-23	0
26-Sep-23	0
27-Sep-23	0
28-Sep-23	0
29-Sep-23	0
30-Sep-23	0
1-Oct-23	0
2-Oct-23	0
3-Oct-23	0
4-Oct-23	0
5-Oct-23	0
6-Oct-23	0
7-Oct-23	0
8-Oct-23	0
9-Oct-23	0
10-Oct-23	0
11-Oct-23	0
12-Oct-23	1
13-Oct-23	1
14-Oct-23	1
15-Oct-23	1
16-Oct-23	1
17-Oct-23	1
18-Oct-23	1
19-Oct-23	1
20-Oct-23	1
21-Oct-23	1
22-Oct-23	1
23-Oct-23	1
24-Oct-23	1
25-Oct-23	1
26-Oct-23	1
27-Oct-23	1
28-Oct-23	1
29-Oct-23	1
30-Oct-23	1
31-Oct-23	1
1-Nov-23	1
2-Nov-23	1
3-Nov-23	1
4-Nov-23	1
5-Nov-23	1
6-Nov-23	1
7-Nov-23	1
8-Nov-23	1
9-Nov-23	1
10-Nov-23	1
11-Nov-23	1
12-Nov-23	1
13-Nov-23	1
14-Nov-23	1
15-Nov-23	1
16-Nov-23	1
17-Nov-23	1
18-Nov-23	1
19-Nov-23	1
20-Nov-23	1
21-Nov-23	1
22-Nov-23	1
23-Nov-23	1
24-Nov-23	1
25-Nov-23	1
26-Nov-23	1
27-Nov-23	1
28-Nov-23	1
29-Nov-23	1
30-Nov-23	1
1-Dec-23	1
2-Dec-23	1
3-Dec-23	1
4-Dec-23	1
5-Dec-23	1
6-Dec-23	1
7-Dec-23	1
8-Dec-23	1
9-Dec-23	1
10-Dec-23	1
11-Dec-23	1
12-Dec-23	1
13-Dec-23	0
14-Dec-23	0
15-Dec-23	0
16-Dec-23	0

[Ahmedx · Accepted solution]

pls try

[jhauw74]

Thank you it works very well 🙂

[Ahmedx]

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Ritaf1983]

Hi @jhauw74 

It seems that one of the values in your dates is not of the date data type. If all values were dates, the formula would be functioning correctly.

for more detailed suggestions please share the tables to work with

If this post helps, then please consider Accepting it as the solution to help the other

members find it more quickly

[Ahmedx]

I think you have a column
Active_Campaign[Campaign Start Period] as text
post the file we can help you

[jhauw74]

Is identify as date value : 

Same goes for Campaign End Period too