Does Item Sell Date Fall Within Two Other Dates?

[Anonymous]

Hello,

I have transactional sales data spanning Q3 of this year. Row level has Item_ID, Trans_Date, and other sales data. from that transaction. I also have a table of promotions spanning the year. This at row level has Item_ID, Promo_ID, Promo_Start_Date, Promo_End_Date, among other information. This table also has duplicates, as items may have been in different promos at different times. 

My goal is to figure out, in the sales data, whether or not that Item had a promo at the time or not. A column named Promo_Sale with "Yes" or "No" would be a great final product.  Consider the following two tables as an example.

Item_ID              Trans_Date                     Promo Sale?

A	Friday, July 08, 2016
A	Saturday, August 20, 2016
A	Friday, September 02, 2016
A	Saturday, July 30, 2016
B	Saturday, August 13, 2016
B	Friday, September 16, 2016
B	Sunday, July 10, 2016
B	Monday, August 01, 2016
C	Friday, September 02, 2016
C	Monday, July 25, 2016
C	Sunday, August 14, 2016
C	Thursday, September 08, 2016
D	Monday, July 04, 2016
D	Monday, August 22, 2016
D	Friday, September 30, 2016
D	Wednesday, July 20, 2016

Item_ID  Promo ID          Promo_Start_Date                        Promo_End_Date

A	1	Thursday, September 01, 2016	Saturday, December 31, 2016
B	1	Thursday, September 01, 2016	Saturday, December 31, 2016
C	2	Friday, July 15, 2016	Sunday, July 31, 2016
A	2	Friday, July 15, 2016	Sunday, July 31, 2016
D	3	Monday, August 01, 2016	Thursday, September 15, 2016

Please help in finding a formula that works. Thank you!

[v-shex-msft · Accepted solution]

Hi @Anonymous,

You can refer to below steps to achieve your requirement.(logic: get the promo sale range, then check the current item's sales date to ensure the result)

Tables.

Calculate the promo sale date range table.(it isn't a real table, it will be stored in the var variable)

Table formula:

Range Table = SELECTCOLUMNS(ADDCOLUMNS(Sheet2,"range",CONCATENATEX(CALENDAR([Promo_Start_Date],[Promo_End_Date]),[Date]&",")),"Item",[Item_ID],"DateRange",[range])

Add calculate column "Promo Sale".

Calculate column:

Promo Sale =
var temp=SELECTCOLUMNS(ADDCOLUMNS(Sheet2,"range",CONCATENATEX(CALENDAR([Promo_Start_Date],[Promo_End_Date]),[Date]&",")),"Item",[Item_ID],"DateRange",[range]) //the range table
return
if(COUNTROWS(FILTER(temp,[Item]=EARLIER(Sheet1[Item_ID])&&ISERROR(SEARCH([Trans_Date],[DateRange]))=FALSE()))>0,TRUE(),FALSE())

Result:

In addition, if you want to use measure to do the same work, you can take a look at below formula:

Promo Sale(Measure) =
Var temp=SELECTCOLUMNS(ADDCOLUMNS(Sheet2,"range",CONCATENATEX(CALENDAR([Promo_Start_Date],[Promo_End_Date]),[Date]&",")),"Item",[Item_ID],"DateRange",[range])
var currentItem=LASTNONBLANK(Sheet1[Item_ID],[Item_ID])
return
if(COUNTROWS(FILTER(temp,[Item]=currentItem&&ISERROR(SEARCH(MAX([Trans_Date]),[DateRange]))=FALSE()))>0,TRUE(),FALSE())

Regards,

Xiaoxin Sheng

[ImkeF]

You can try a variation of this method: http://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/

Just create a concatenated key of ItemID and TransDate in order to produce an 1:n relationship between the tables.