Fetching the added/removed records from a table

Parkavi — Mon, 17 Feb 2020 09:52:55 GMT

Hi,

Please help me in solving the below issue using DAX

I have a quarterly data with list of items as below.

Date | Items | Quarter

01/01/2020 | A | Current Quarter

01/01/2020 | B | Current Quarter

01/01/2020 | C | Current Quarter

01/01/2020 | D | Current Quarter

01/12/2019 | A | Previous Quarter

01/12/2019 | D | Previous Quarter

01/12/2019 | F | Previous Quarter

Here I need to fetch the added the items and removed items in the current quarter comparing to previous quarter

Added Items

Removed Items

Please anyone could tell me how to fetch the added and remvoed items using DAX...

Thanks in advance.

Re: Fetching the added/removed records from a table

ibarrau — Mon, 17 Feb 2020 13:32:16 GMT

Hi! interesting situation. If you have a good refresh of current and previous quarter flag column you can build columns like this for new:

IsNew = IF ( AND( newfromquarter[Quarter] = "Current Quarter"; NOT( newfromquarter[Items] IN SELECTCOLUMNS( FILTER( VALUES(newfromquarter); newfromquarter[Quarter] = "Previous Quarter" ); "Item"; newfromquarter[Items] ) ) ) ; "Yes" ; "No" )

And something like this for the removed:

IsRemoved = IF ( AND( newfromquarter[Quarter] = "Previous Quarter"; NOT( newfromquarter[Items] IN SELECTCOLUMNS( FILTER( VALUES(newfromquarter); newfromquarter[Quarter] = "Current Quarter" ); "Item"; newfromquarter[Items] ) ) ) ; "Yes" ; "No" )

Then you can just filter in your report pages by this new column to understand the added and removed items.

Hope this helps,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Fetching the added/removed records from a table in DAX Commands and Tips

Fetching the added/removed records from a table

Re: Fetching the added/removed records from a table