Filter and slice selected column

[glimpse111]

Hi guys,

I need some help to apply filter to the following data. Some of customers are suspended from May 18 onwards so their sales number should not be included from May onwards. How can I apply filter which will include the data for selected customer from Oct-April but not from May-Sep. Is there anyway I can do this without removing the numbers from rows as I would need these numbers to tell what is the sale for suspended customers from May-Sep.

Many thanks in advance!

ID	Customer Name	Status	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	YTD	Total
16CR05	A	Active	11.13	11.82	12.11	12.88	11.61	11.79	10.22	9.84	9.77	9.94	9.74	10.05	111.10	130.90
16CR10	B	Active	10.02	10.64	10.90	11.59	10.45	10.61	9.20	8.86	8.80	8.94	8.76	9.05	100.00	117.81
16CR12	C	Active	3.34	3.55	3.63	3.86	3.48	3.54	3.07	2.95	2.93	2.98	2.92	3.02	33.33	39.27
16CR13	D	Active	4.45	4.73	4.85	5.15	4.64	4.72	4.09	3.94	3.91	3.97	3.89	4.02	44.44	52.36
16CR21	E	Active	11.13	11.82	12.11	12.88	11.61	11.79	10.22	9.84	9.77	9.94	9.74	10.05	111.10	130.90
16CR24	F	Active	4.45	4.73	4.85	5.15	4.64	4.72	4.09	3.94	3.91	3.97	3.89	4.02	44.44	52.36
16CR28	G	Active	13.35	14.19	14.54	15.45	13.93	14.15	12.26	11.81	11.73	11.92	11.68	12.07	133.33	157.08
16CR29	H	Active	13.35	14.19	14.54	15.45	13.93	14.15	12.26	11.81	11.73	11.92	11.68	12.07	133.33	157.08
16CR35	I	Active	11.13	11.82	12.11	12.88	11.61	11.79	10.22	9.84	9.77	9.94	9.74	10.05	111.10	130.90
16CR47	J	Active	11.13	11.82	12.11	12.88	11.61	11.79	10.22	9.84	9.77	9.94	9.74	10.05	111.10	130.90
16CR50	K	Suspended from Jan	11.13	11.82	12.11	12.88	11.61	11.79	10.22	9.84	9.77	9.94	9.74	10.05	111.10	130.90
16CR53	L	Suspended	10.02	10.64	10.90	11.59	10.45	10.61	9.20	8.86	8.80	8.94	8.76	9.05	100.00	117.81
16CR55	M	Suspended	11.13	11.82	12.11	12.88	11.61	11.79	10.22	9.84	9.77	9.94	9.74	10.05	111.10	130.90
16CR57	N	Suspended	11.13	11.82	12.11	12.88	11.61	11.79	10.22	9.84	9.77	9.94	9.74	10.05	111.10	130.90

[v-shex-msft · Accepted solution]

Hi @glimpse111,

Is there any columns store suspend date?
If this is a case, you can write a measure to use current date ,customer name and id to find out related suspend date, then add if statement to replace records who after suspend date. Use this measure to replace value column.

Sample:

Replaced result =
VAR currDate =
    MAX ( Table[Date] )
VAR currCustomer =
    SELECTEDVALUE ( Table[Customer Name] )
VAR currID =
    SELECTEDVALUE ( Table[ID] )
VAR suspended =
    CALCULATE (
        MAX ( Table[Suspend] ),
        FILTER ( ALL ( Table ), [ID] = currID && [Customer Name] = currCustomer )
    )
RETURN
    IF (
        currDate <= suspended
            || suspended = BLANK (),
        SUM ( table[Amount] ),
        IF ( currDate > suspended, blank )
    )

Regards,

Xiaoxin Sheng