Complex Join

[Anonymous]

Hi All,

I have a list of current subscribers for every week for the past year (inlcudes customer ID) that I have appended into one table and included a column 'Report Date'. I want to create 2 additional tables off this data: (1) Starts (customers that were on the current week report but not on the previous) and (2) Stops (customers that were on the previous weeks report but not on the current one). 

Is this possible to do in PowerBI? Is there another tool that would be better for this? Any help would be appreciated.

Thank you! 

[ryan_mayu]

could you pls provide some sample data and expected output?

[SamWiseOwl · Accepted solution]

Hi @Anonymous 

I've mocked up your data and created 3 tables.

The first is filtered to this week and the other two are filtered to last week.

On the In last week I have used a merge to the original table on the customer ID and set it to inner join (only rows in both tables)

For the Not in last week do the same thing but change the join to Left Anti (Rows in this table NOT in the other table).