cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jt22 Frequent Visitor
Frequent Visitor

Removing Data from Rows Using Subtraction

Hello All,

 

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.

 

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? 

 

Thank you in advance for your help.

 

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

Accepted Solutions
HotChilli Senior Member
Senior Member

Re: Removing Data from Rows Using Subtraction

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] <= _Day && Hotel[Item] = _Item ))

And then create a new table by filtering (where Column > x )

 

NewTable = FILTER(Hotel, Hotel[Column] > 4)
2 REPLIES 2
HotChilli Senior Member
Senior Member

Re: Removing Data from Rows Using Subtraction

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] <= _Day && Hotel[Item] = _Item ))

And then create a new table by filtering (where Column > x )

 

NewTable = FILTER(Hotel, Hotel[Column] > 4)
jt22 Frequent Visitor
Frequent Visitor

Re: Removing Data from Rows Using Subtraction

thank you for your help.