Sales identified that were made during promo campaigns

[EugeneNel83]

Hi All

I've been trying to lookup if sales were made on promo or not. 

The promo table has unique time periods but multiple times that a code is on promo.  I'll include a simplified example of what the tables look like below:

Sales Table
Code	Date	Qty
12345	2022/04/15	1
12345	2022/07/03	2
123456	2022/01/01	5

Promo Table
Code	Date Start	Date End
12345	2022/03/01	2022/03/31
12345	2022/07/01	2022/07/22
123456	2019/12/01	2019/12/31

The result should look like this where the promo lines are identified  :

Sales Table			Added Column
Code	Date	Qty	On Promo
12345	2022/04/15	1	FALSE
12345	2022/07/03	2	TRUE
123456	2022/01/01	5	FALSE

I'm trying to adapt this code below to include a logical lookup for rows that meet the date range and then product code to return the rows. Then I would like to add a True or False if on promo or not. 

Sales=

ADDCOLUMNS(

Sales,

"OnPromo",

VAR SalesDate =

SalesTable[Date]

RETURN

IF(LOOKUPVALUE (

IF(PromoTable[StartDate]>=SalesDate&&PromoTable[EndDate]<=SalesDate), SalesDate

Promo[Code], Sales[Code]),"True","False"),

)

Kind regards

Eugene

[v-xiaotang · Accepted solution]

Hi @EugeneNel83 

Thanks for reaching out to us.

>>

You can try this measure, 

On Promo = IF( CALCULATE(COUNTROWS(PromoTable),FILTER(PromoTable,PromoTable[Code]=MIN(SalesTable[Code]) && PromoTable[Date Start]<=MIN(SalesTable[Date]) && PromoTable[Date End]>=MIN(SalesTable[Date])))=BLANK(),false,TRUE)

 if you need a column, try this,

column = IF( CALCULATE(COUNTROWS(PromoTable),FILTER(PromoTable,PromoTable[Code]=EARLIER(SalesTable[Code]) && PromoTable[Date Start]<=EARLIER(SalesTable[Date]) && PromoTable[Date End]>=EARLIER(SalesTable[Date])))=BLANK(),false,TRUE)

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[EugeneNel83]

Thanks Xiotang- perfect! Much appreciated

[amitchandak]

@EugeneNel83 , A new column in the sales table

column =

var _cnt = countx(promo, promo[code] = sales[code] && promo[start Date] <= sales[Date] && promo[End Date] >= sales[Date] ),promo[code])
return
if(isblank(_cnt), false(), True())

[EugeneNel83]

Thanks Amit, I get an error  on the promo[code]. It says the syntax for ',' is incorrect.  Is there a way around this?