Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Kind regards
Eugene
Solved! Go to 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.
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.
Thanks Xiotang- perfect! Much appreciated
@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())
Thanks Amit, I get an error on the promo[code]. It says the syntax for ',' is incorrect. Is there a way around this?