Removing Data from Rows Using Subtraction

Question

Hello All,&nbsp;I am a newish user and I have been trying to figure this out for a couple days, so I would appreciate all the help I can get.&nbsp;Sorting by Date (Oldest to Newest), I would like to remove the last 4 entries for each Item. The Dates are different for each Item, so I couldn't just unpivot and remove the rows. The Items # and name will change so I don't think I can just separate them into their own queries?&nbsp;&nbsp;Thank you in advance for your help.&nbsp;DateItemAmount1/1/2019Madison&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 14,5952/1/2019Madison&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 11,3423/1/2019Madison&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (3,412)4/1/2019Madison&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 13,8895/1/2019Madison&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,3936/1/2019Madison&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 54,1897/1/2019Madison&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 26,4528/1/2019Madison&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,4139/1/2019Madison&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 29,3441/1/2019Blake&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17,9182/1/2019Blake&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17,9903/1/2019Blake&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17,9824/1/2019Blake&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 17,9845/1/2019Blake&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (262,889)6/1/2019Blake&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 71,8441/1/2019Atrium&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,3882/1/2019Atrium&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5,3893/1/2019Atrium&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (982,410)4/1/2019Atrium&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (3,273)5/1/2019Atrium&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 104,9676/1/2019Atrium&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 104,9687/1/2019Atrium&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 76,5948/1/2019Atrium&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 66,7251/1/2019Daybreak&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 147,5212/1/2019Daybreak&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 147,7103/1/2019Daybreak&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 147,6904/1/2019Daybreak&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 147,713

hotchilli · Accepted Answer

In DAX you could add a calculated column to COUNT per category e.g.Column = VAR _Item = Hotel[Item]
         VAR _Day = Hotel[Date]
RETURN  
 CALCULATE(COUNT(Hotel[Item]), FILTER(Hotel, Hotel[Date] &lt;= _Day &amp;&amp; Hotel[Item] = _Item ))And then create a new table by filtering (where Column &gt; x )&nbsp;NewTable = FILTER(Hotel, Hotel[Column] &gt; 4)

Forum Discussion

Removing Data from Rows Using Subtraction

2 Replies

Related content

Subtraction calulation

Cumulative Subtraction using Criteria

Can't subtract

remove duplicate value within a row

Subtract previous days using M-Query

Date	Item	Amount
1/1/2019	Madison	14,595
2/1/2019	Madison	11,342
3/1/2019	Madison	(3,412)
4/1/2019	Madison	13,889
5/1/2019	Madison	3,393
6/1/2019	Madison	54,189
7/1/2019	Madison	26,452
8/1/2019	Madison	1,413
9/1/2019	Madison	29,344
1/1/2019	Blake	17,918
2/1/2019	Blake	17,990
3/1/2019	Blake	17,982
4/1/2019	Blake	17,984
5/1/2019	Blake	(262,889)
6/1/2019	Blake	71,844
1/1/2019	Atrium	5,388
2/1/2019	Atrium	5,389
3/1/2019	Atrium	(982,410)
4/1/2019	Atrium	(3,273)
5/1/2019	Atrium	104,967
6/1/2019	Atrium	104,968
7/1/2019	Atrium	76,594
8/1/2019	Atrium	66,725
1/1/2019	Daybreak	147,521
2/1/2019	Daybreak	147,710
3/1/2019	Daybreak	147,690
4/1/2019	Daybreak	147,713