Equivalent of SQL subquery

[ubv]

Hi,

I have a simple table Sales with the daily sales per client:

day           client.         spentUSD

20200501.    a.               5000

20200501.    b.                490

20200502.    b                3000

....

I need to build a dashboard where the user can select two days and they can see the clients gained/lost: 

- two tables: one with the clients gained (in the example above empty) and another table with the clients lost (client #a)

- two cards: showing the number of clients gained (0) and lost (1)

Conceptually it's very straight-forward, but I cannot make it work. In SQL I'd use something like :

select * from Sales where day = $selectedDay1 and client not in (select client from Sales where day = $selectedDay2)

Any ideas? I tried using "NOT IN" but couldn't make it work with dynamic values (the ones in the slicers).

Thanks,

[v-deddai1-msft]

Hi @ubv ,

Why you need two selected days to calculate client gained and lost? Would you please tell me what is the logic for it?

In my mind, we just need one selected day:

Gained client: clients who appeared on the selected date and have not appeared before

Lost client: clients who have not appeared on the selected date and appeared before.

Is my idea correct, please correct me?

Best Regards,

Dedmon Dai

[camargos88 · Accepted solution]

Hi @ubv ,

Check this link: https://www.daxpatterns.com/new-and-returning-customers/

Did I answer your question? Mark my post as a solution!
Ricardo