Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Does Item Sell Date Fall Within Two Other Dates?

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?

AFriday, July 08, 2016 
ASaturday, August 20, 2016 
AFriday, September 02, 2016 
ASaturday, July 30, 2016 
BSaturday, August 13, 2016 
BFriday, September 16, 2016 
BSunday, July 10, 2016 
BMonday, August 01, 2016 
CFriday, September 02, 2016 
CMonday, July 25, 2016 
CSunday, August 14, 2016 
CThursday, September 08, 2016 
DMonday, July 04, 2016 
DMonday, August 22, 2016 
DFriday, September 30, 2016 
DWednesday, July 20, 2016 

 

Item_ID  Promo ID          Promo_Start_Date                        Promo_End_Date

A1Thursday, September 01, 2016Saturday, December 31, 2016
B1Thursday, September 01, 2016Saturday, December 31, 2016
C2Friday, July 15, 2016Sunday, July 31, 2016
A2Friday, July 15, 2016Sunday, July 31, 2016
D3Monday, August 01, 2016Thursday, September 15, 2016

 

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

  • Anonymous's avatar
    Anonymous
    9 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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