DAX Code to Show Data Changes

[matsahiro]

I'm trying to display top performing products where the price is greater than 50 and the quantity sold is greater than 60. The table is only supposed to display our products that meet this requirement, but also show products that were recently dropped from that previous list from last quarter for not making a certain requirement now. Any ideas on writing the DAX to make this "dropped/added" column?

Date 1/1/2021, Products with price>50 and quantity sold>60

Previous Report

Product	Price	Quantity Sold
Shoes	60	60
Shirt	60	70
Short	70	60
Pants	100	80

Date 3/31/2021, Products with price>50 and quantity sold>60, includes products that were dropped from Previous Report -

New View with Dropped/Added Indicator

Product	Price	Quantity Sold	Dropped/Added
Hat	55	70	Added
Shirt	40	70	Dropped - Due to Price
Shoes	60	60
Short	70	60
Pants	100	10	Dropped - Due to Quantity Sold

How do I create a view of these top performing products by those filters but also include "dropped" products that do not meet these requirements anymore? 

Thank you

[CNENFRNL · Accepted solution]

@matsahiro , your question is interesting and the solution is tricky.

First of all, it's for sure that items in Product column is unique since it's an aggregated monthly sales report. Thus, a 1:1 relationship can be created between any of two reports this way,

Accordingly, the measure is

Dropped / Added = 
SWITCH (
    TRUE (),
    ISEMPTY ( CALCULATETABLE ( Previous ) ), "Added",
    MAX ( 'Current'[Price] ) < 50, "Dropped - Due to Price",
    MAX ( 'Current'[Quantity Sold] ) < 60, "Dropped - Due to Quantity Sold",
    ""
)

[matsahiro]

@CNENFRNL Unfortunately it seems I can't open the PBIX, probably due to me having a different desktop version. It is possible to attach screenshots of the data you entered in for "Current"? I'm curious as to whether you only included products that met all the requirements or not in this "current" data table. Basically im trying to figure out how you populated the table view with the dropped products from the previous table if the filters overall are set to price>50 and quantity sold>60, considering how the dropped products no longer meet the requirements to be in the view

[CNENFRNL]

Hi, @matsahiro , simple enough, Current table is like this,

ProductPriceQuantity Sold

Hat	55	70
Shirt	40	70
Shoes	60	60
Short	70	60
Pants	100	10

[matsahiro]

@CNENFRNL Okay, great. Last question. I understand the data and how you created the add/dropped column. But, if we are creating a table visual where the visual level filters are set to price>50 and quantity sold>60, how do I bring in the products into the view that used to meet this requirement the last period but no longer do now? (referring to displaying the "dropped" products in the view)

[matsahiro]

@CNENFRNL  This helps out greatly. Thank you very much for your help!