Fetching the added/removed records from a table

[Parkavi]

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

B

C

Removed Items

F

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

Thanks in advance.

[ibarrau]

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.