cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## 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?

 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
1 ACCEPTED SOLUTION
Super User

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
Super User

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)`
Anonymous
Not applicable

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors