Sum multiple results from COUNTROWS

[clo_64]

Hi,

I would like to count all the New Customers for the actual year (= YTD).

My measure works very well when I use it and build a table with the date 'Date'[Date]

But how can I sum all the COUNTROWS to get the number of customers since Jan 2022.

Here the measure I use to get the New Customer / month. How can I change this with SUMX? to get a sum for 2022?

New Customers YTD =
VAR currentCustomers = VALUES('SalesReport'[Customer])
VAR currentDate = MIN('Date'[Date])
VAR Period = 'Period in months'[Period in months Value]*30
VAR pastCustomers = CALCULATETABLE(VALUES('SalesReport'[Customer]),
ALL('Date'[Date]),
'Date'[Date]<currentDate && 'Date'[Date] + Period >currentDate)

VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)

RETURN COUNTROWS(newCustomers)

Thanks

[Anonymous]

Hi @clo_64 ,

Try:

measure =
SUMX (
    FILTER ( ALLSELECTED ( 'date' ), 'date'[date] >= DATE ( 2022, 1, 1 ) ),
    [New Customers YTD]
)

If I misunderstood your meaning, please share some sample data.

Best Regards,

Jay

[clo_64]

Hi Jay,
The result that I get is not correct! The measure gives me 44K, and I expect around 400.
I'll try to debug in DAXstudio and share my findings later
Thanks

[speedramps]

Sorry I am a unpaid volunteer and busy until next week.

Hope another Super User can help in the meantime.

Otherwise I will fix it next week

[clo_64]

fine! I understand...
Next week is OK

[speedramps]

Try this 

Total New Customers =
/* DOCUMENTATION
Get number of new customers as follows:-
Use addcolumns to get a set with (Customer,Previous Rows)
Then filter the set to just to include Customers with no previous rows.
Then count the number of Customers
*/

VAR mindate = DATE(2022,01,01)

VAR NewCustomers =
FILTER (
ADDCOLUMNS (
VALUES ( 'SalesReport'[Customer] ),
"PreviousRows",
CALCULATE (COUNTROWS ('SalesReport'),
FILTER (ALL ( 'Date'[Date]),'Date'[Date]< mindate))
),
[PreviousRows] = 0
)

RETURN
COUNTROWS(NewCustomers)

Then drag  [Total New Customers] yo a card visual with no filters

Thanks for reaching out for help.

I have helped you, now please help me by giving kudos.

Remeber we are unpaid volunteers.

Click the thumbs up and accept as solution button. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !

[clo_64]

Thanks this is going in the right direction, but not exactly what I was looking for.
Here you are calculating the New Customers by comparing the customers in 2022 with the customers in 2021.
In my measure, I defined a new customer as someone I did not see for a certain rolling period:
'Date'[Date]<currentDate && 'Date'[Date] + Period >currentDate
and then I want to sum up all the new customers in 2022.
Thanks in advance