Filter and slice selected column

Question

Hi guys,&nbsp;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.&nbsp;Many thanks in advance!&nbsp;&nbsp;&nbsp;IDCustomer NameStatusOct-17Nov-17Dec-17Jan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18YTDTotal16CR05AActive&nbsp;&nbsp;&nbsp;&nbsp; 11.13&nbsp;&nbsp;&nbsp;&nbsp; 11.82&nbsp;&nbsp;&nbsp;&nbsp; 12.11&nbsp;&nbsp;&nbsp;&nbsp; 12.88&nbsp;&nbsp;&nbsp;&nbsp; 11.61&nbsp;&nbsp;&nbsp;&nbsp; 11.79&nbsp;&nbsp;&nbsp;&nbsp; 10.22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.84&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.77&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.94&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.74&nbsp;&nbsp;&nbsp;&nbsp; 10.05&nbsp;&nbsp; 111.10&nbsp;&nbsp; 130.9016CR10BActive&nbsp;&nbsp;&nbsp;&nbsp; 10.02&nbsp;&nbsp;&nbsp;&nbsp; 10.64&nbsp;&nbsp;&nbsp;&nbsp; 10.90&nbsp;&nbsp;&nbsp;&nbsp; 11.59&nbsp;&nbsp;&nbsp;&nbsp; 10.45&nbsp;&nbsp;&nbsp;&nbsp; 10.61&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.86&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.94&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.76&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.05&nbsp;&nbsp; 100.00&nbsp;&nbsp; 117.8116CR12CActive&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.34&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.55&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.63&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.86&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.48&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.54&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.07&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.95&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.93&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.98&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.92&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.02&nbsp;&nbsp;&nbsp;&nbsp; 33.33&nbsp;&nbsp;&nbsp;&nbsp; 39.2716CR13DActive&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.45&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.73&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.85&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.64&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.72&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.09&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.94&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.91&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.97&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.89&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.02&nbsp;&nbsp;&nbsp;&nbsp; 44.44&nbsp;&nbsp;&nbsp;&nbsp; 52.3616CR21EActive&nbsp;&nbsp;&nbsp;&nbsp; 11.13&nbsp;&nbsp;&nbsp;&nbsp; 11.82&nbsp;&nbsp;&nbsp;&nbsp; 12.11&nbsp;&nbsp;&nbsp;&nbsp; 12.88&nbsp;&nbsp;&nbsp;&nbsp; 11.61&nbsp;&nbsp;&nbsp;&nbsp; 11.79&nbsp;&nbsp;&nbsp;&nbsp; 10.22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.84&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.77&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.94&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.74&nbsp;&nbsp;&nbsp;&nbsp; 10.05&nbsp;&nbsp; 111.10&nbsp;&nbsp; 130.9016CR24FActive&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.45&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.73&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.85&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5.15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.64&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.72&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.09&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.94&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.91&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.97&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3.89&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4.02&nbsp;&nbsp;&nbsp;&nbsp; 44.44&nbsp;&nbsp;&nbsp;&nbsp; 52.3616CR28GActive&nbsp;&nbsp;&nbsp;&nbsp; 13.35&nbsp;&nbsp;&nbsp;&nbsp; 14.19&nbsp;&nbsp;&nbsp;&nbsp; 14.54&nbsp;&nbsp;&nbsp;&nbsp; 15.45&nbsp;&nbsp;&nbsp;&nbsp; 13.93&nbsp;&nbsp;&nbsp;&nbsp; 14.15&nbsp;&nbsp;&nbsp;&nbsp; 12.26&nbsp;&nbsp;&nbsp;&nbsp; 11.81&nbsp;&nbsp;&nbsp;&nbsp; 11.73&nbsp;&nbsp;&nbsp;&nbsp; 11.92&nbsp;&nbsp;&nbsp;&nbsp; 11.68&nbsp;&nbsp;&nbsp;&nbsp; 12.07&nbsp;&nbsp; 133.33&nbsp;&nbsp; 157.0816CR29HActive&nbsp;&nbsp;&nbsp;&nbsp; 13.35&nbsp;&nbsp;&nbsp;&nbsp; 14.19&nbsp;&nbsp;&nbsp;&nbsp; 14.54&nbsp;&nbsp;&nbsp;&nbsp; 15.45&nbsp;&nbsp;&nbsp;&nbsp; 13.93&nbsp;&nbsp;&nbsp;&nbsp; 14.15&nbsp;&nbsp;&nbsp;&nbsp; 12.26&nbsp;&nbsp;&nbsp;&nbsp; 11.81&nbsp;&nbsp;&nbsp;&nbsp; 11.73&nbsp;&nbsp;&nbsp;&nbsp; 11.92&nbsp;&nbsp;&nbsp;&nbsp; 11.68&nbsp;&nbsp;&nbsp;&nbsp; 12.07&nbsp;&nbsp; 133.33&nbsp;&nbsp; 157.0816CR35IActive&nbsp;&nbsp;&nbsp;&nbsp; 11.13&nbsp;&nbsp;&nbsp;&nbsp; 11.82&nbsp;&nbsp;&nbsp;&nbsp; 12.11&nbsp;&nbsp;&nbsp;&nbsp; 12.88&nbsp;&nbsp;&nbsp;&nbsp; 11.61&nbsp;&nbsp;&nbsp;&nbsp; 11.79&nbsp;&nbsp;&nbsp;&nbsp; 10.22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.84&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.77&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.94&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.74&nbsp;&nbsp;&nbsp;&nbsp; 10.05&nbsp;&nbsp; 111.10&nbsp;&nbsp; 130.9016CR47JActive&nbsp;&nbsp;&nbsp;&nbsp; 11.13&nbsp;&nbsp;&nbsp;&nbsp; 11.82&nbsp;&nbsp;&nbsp;&nbsp; 12.11&nbsp;&nbsp;&nbsp;&nbsp; 12.88&nbsp;&nbsp;&nbsp;&nbsp; 11.61&nbsp;&nbsp;&nbsp;&nbsp; 11.79&nbsp;&nbsp;&nbsp;&nbsp; 10.22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.84&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.77&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.94&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.74&nbsp;&nbsp;&nbsp;&nbsp; 10.05&nbsp;&nbsp; 111.10&nbsp;&nbsp; 130.9016CR50KSuspended from Jan&nbsp;&nbsp;&nbsp;&nbsp; 11.13&nbsp;&nbsp;&nbsp;&nbsp; 11.82&nbsp;&nbsp;&nbsp;&nbsp; 12.11&nbsp;&nbsp;&nbsp;&nbsp; 12.88&nbsp;&nbsp;&nbsp;&nbsp; 11.61&nbsp;&nbsp;&nbsp;&nbsp; 11.79&nbsp;&nbsp;&nbsp;&nbsp; 10.22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.84&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.77&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.94&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.74&nbsp;&nbsp;&nbsp;&nbsp; 10.05&nbsp;&nbsp; 111.10&nbsp;&nbsp; 130.9016CR53LSuspended&nbsp;&nbsp;&nbsp;&nbsp; 10.02&nbsp;&nbsp;&nbsp;&nbsp; 10.64&nbsp;&nbsp;&nbsp;&nbsp; 10.90&nbsp;&nbsp;&nbsp;&nbsp; 11.59&nbsp;&nbsp;&nbsp;&nbsp; 10.45&nbsp;&nbsp;&nbsp;&nbsp; 10.61&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.86&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.80&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.94&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8.76&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.05&nbsp;&nbsp; 100.00&nbsp;&nbsp; 117.8116CR55MSuspended&nbsp;&nbsp;&nbsp;&nbsp; 11.13&nbsp;&nbsp;&nbsp;&nbsp; 11.82&nbsp;&nbsp;&nbsp;&nbsp; 12.11&nbsp;&nbsp;&nbsp;&nbsp; 12.88&nbsp;&nbsp;&nbsp;&nbsp; 11.61&nbsp;&nbsp;&nbsp;&nbsp; 11.79&nbsp;&nbsp;&nbsp;&nbsp; 10.22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.84&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.77&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.94&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.74&nbsp;&nbsp;&nbsp;&nbsp; 10.05&nbsp;&nbsp; 111.10&nbsp;&nbsp; 130.9016CR57NSuspended&nbsp;&nbsp;&nbsp;&nbsp; 11.13&nbsp;&nbsp;&nbsp;&nbsp; 11.82&nbsp;&nbsp;&nbsp;&nbsp; 12.11&nbsp;&nbsp;&nbsp;&nbsp; 12.88&nbsp;&nbsp;&nbsp;&nbsp; 11.61&nbsp;&nbsp;&nbsp;&nbsp; 11.79&nbsp;&nbsp;&nbsp;&nbsp; 10.22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.84&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.77&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.94&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.74&nbsp;&nbsp;&nbsp;&nbsp; 10.05&nbsp;&nbsp; 111.10&nbsp;&nbsp; 130.90

anonymous · Accepted Answer

Hi&nbsp;glimpse111,
&nbsp;
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.
&nbsp;
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 &amp;&amp; [Customer Name] = currCustomer )
    )
RETURN
    IF (
        currDate &lt;= suspended
            || suspended = BLANK (),
        SUM ( table[Amount] ),
        IF ( currDate &gt; suspended, blank )
    )

&nbsp;
Regards,
Xiaoxin Sheng

Forum Discussion

Filter and slice selected column

1 Reply

Related content

Slicer for filtering several columns

Dynamic Column visibility in table visual based on filter selection

Create Calculated Column based on filter Selection

Column selection based on Filter

Dynamic Filtering based on date selection

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