Check if row exist in former week and remove rows based on result

[Anonymous]

The problem comes with this source data imported from a folder -- multiple tables appended together 

Item No.	Date	Buying Price	Selling Price	Margin
1	4/20/2020	4	5	1
1	4/16/2020	4	5	1
2	4/16/2020	4	5	1

The problem here is I created a matrix to show the margin over time like this:

Item No\Margin\Date	4/16/2020	4/20/2020
1	1	1
2	1

In this case, the margin for item 2 would be blank.

I'm wondering if there is a way that I can search for items that did not exist for the latest data table and delete its record in the former data? For instance, I want to delete the item 2 if it didn't exist in the latest table.

Thanks for any help.

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

You may create a measure as below.

IsDisplay = 
var itemno = SELECTEDVALUE('Table'[Item No.])
var latestdate = 
CALCULATE(
    MAX('Table'[Date]),
    FILTER(
         ALLSELECTED('Table'),
         'Table'[Item No.] = itemno
    )
)
return
IF(
    latestdate = TODAY(),
    1,
    0
)

Then you need to put the measure in the visual level filter. Today is 4/21/2020. Here is the result.

Best Regards

Allan

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

[Anonymous]

Hi, thanks for your reply. I might not explain clearly before. 

Here are two original tables:

1 - week 16, with 8 items

week 16

 2- week 17: with 7 items, item 8 was discontinued in week 17. 

week 17

As I imported these data from a folder, it was appended to one sheet like this:

PowerBI Import

Based on the imported data, I created a matrix to compare the margin over time:

PowerBI Matrix

what I see here, item 8's margin in week 17 is blank because it's discontinued. In this case, I don't want to see item 8 at all, since it's already been discontinued, I really don't care about its past performance. So my question here is, how can I mark item 8 and filter it out?

I'm trying to find a way to compare item numbers and filter out those discontinued items.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I modified data to reproduce your scenario.

Table:

You may create a measure as follows.

Visual Control = 
var itemno = SELECTEDVALUE('Table'[Item No])
var _maxdate =
CALCULATE(
    MAX('Table'[Date]),
    ALLSELECTED('Table')
)
var latestweek = 
CALCULATETABLE(
    DISTINCT('Table'[SourceName]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Date] = _maxdate
    )
)
var margin =
CALCULATE(
    SUM('Table'[Margin]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Item No] = itemno&&
        'Table'[Date] = _maxdate&&
        'Table'[SourceName] in latestweek
    )
)
return
IF(
    NOT( ISBLANK(margin) ),
    1,
    0
)

Finally you may put it in the visual level filter to display the result.

Best Regards

Allan

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

[Greg_Deckler]

You want to delete it, delete it, or just not show it?

[Anonymous]

Thanks for your reply. Yes, actually I just want to hide it rather than delete the record. 

I am looking for a way to compare the current week's item number and former week's item number to mark those items existed before but not now. 

based on the marked column (1 or 0), I can apply that as a filter to the visual.