Forum Discussion

clo_64's avatar
clo_64
Frequent Visitor
4 years ago

Sum multiple results from COUNTROWS

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

Need Help

6 Replies

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      clo_64
      Frequent Visitor
      4 years ago

      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

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      clo_64
      Frequent Visitor
      4 years ago

      fine! I understand...
      Next week is OK

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      clo_64
      Frequent Visitor
      4 years ago

      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