Flag against orders which were placed when a code was run

[aJ2]

Hi.

I have a table of orders where the revenue, coupon used, validity period etc is mentioned. There are some instances wherein the on the days coupons were run, in some orders no coupon was used.

For example,

date	order id	total value	coupon code	coupon valid from	coupon valid till
23-Dec-17	1001	100	MC10	23-Dec-17	24-Dec-17
23-Dec-17	1002	32	MC10	23-Dec-17	24-Dec-17
23-Dec-17	1003	342
23-Dec-17	1004	3534	MC10	23-Dec-17	24-Dec-17
23-Dec-17	1005	596	MC10	23-Dec-17	24-Dec-17
23-Dec-17	1006	50
24-Dec-17	1007	43
24-Dec-17	1008	564	MC10	23-Dec-17	24-Dec-17
24-Dec-17	1009	31
24-Dec-17	1010	3265	MC10	23-Dec-17	24-Dec-17
24-Dec-17	1011	9956
24-Dec-17	1012	213	MC10	23-Dec-17	24-Dec-17
24-Dec-17	1013	635	MC10	23-Dec-17	24-Dec-17
25-Dec-17	1014	45
25-Dec-17	1015	646
25-Dec-17	1016	458
25-Dec-17	1017	98

So I want a new column to identify if a coupon code has been used on that day. So against order id 1009, I want True against it as the order has been placed during the time a coupon code is running.

Kindly guide

[v-yuezhe-msft · Accepted solution]

@aJ2,

Please create the following columns in your table.

Index = RANKX(FILTER(Table1,Table1[order id]<EARLIER(Table1[order id])),Table1[order id],,ASC)

prefromdate = 
VAR LastNonBlankIndex =
    CALCULATE (
        LASTNONBLANK ( Table1[Index], 1 ),
        FILTER (
            ALL ( Table1 ),
           Table1[Index] <= EARLIER ( Table1[Index] )
                && NOT ( ISBLANK ( Table1[coupon valid from]) )
        )
    )
RETURN
    CALCULATE (
        MAX(Table1[coupon valid from]),
        FILTER ( ALL ( Table1 ), Table1[Index] = LastNonBlankIndex)
    )

pretodate = 
VAR LastNonBlankIndex =
    CALCULATE (
        LASTNONBLANK ( Table1[Index], 1 ),
        FILTER (
            ALL ( Table1 ),
           Table1[Index] <= EARLIER ( Table1[Index] )
                && NOT ( ISBLANK ( Table1[coupon valid from]) )
        )
    )
RETURN
    CALCULATE (
        MAX(Table1[coupon valid till]),
        FILTER ( ALL ( Table1 ), Table1[Index] = LastNonBlankIndex )
    )

check = IF(AND(Table1[date]<=Table1[pretodate],Table1[date]>=Table1[prefromdate])&&Table1[coupon code]=BLANK(),TRUE())

Regards,
Lydia