Count rows w/ multiple criteria across 2 tables

[Anonymous]

Hello and thanks in advance for any help!

I am trying to create a measure(s) that will count the number of clients who are both new in 2019 AND made a purchase in a particular month, for example 12/2019.

Lets say I have two tables, "Clients" and "Purchases". The number of clients in the "Clients" table new in 2019 are 3, clients 6, 8 and 11. The number who made purchases in the "Purchases" table is also 3, clients 6, 8 and 5.

 The number in who meet BOTH criteria is 2, clients 6 and 8, as 11 did not purchase in 12/2019 nd client 5, who did, was new in 2017 not 2019, I need a measure that will return the result of "2".

Please help!! Thank you!

[Anonymous]

Thank you both for your help! It was much appreciated but neither actually worked the way I had hoped. I ended up attacking this problem from a whole different angle, so the assistance with this issue is no longer needed. Thanks!

[v-yiruan-msft]

Hi @Anonymous ,

You can create one measure as below:

GetCustforP =

VAR c = MAX('Purchases'[Client ID])

VAR c1 =

    IF (

            YEAR ( MAX('Purchases'[Purchase Date] ))

                = YEAR (

                    CALCULATE (

                        MAX ( 'Clients'[Start Date] ),

                        FILTER ( 'Clients', 'Clients'[Client ID] = c )

                    )

                ),

        MAX (Purchases[Client ID])

    )

var C2=IF(FORMAT(MAX('Purchases'[Purchase Date]),"YYYYMM")="201912",c1,BLANK())

RETURN

   c2

Best Regards

Rena

[amitchandak]

This is a bit costly. But this will anew column with first purchase date

minx(filter(purchases,purchases[purchase date]<=earlier(purchases[purchase date])),purchases[purchase date])

Now same came used for rest of your logic

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin