If statement not totalling

[Charli2516]

Hello

I need some help with an IF Statement not totalling.

I am trying to calculate new customers and lost customers. I have a rolling 12 months calculation current year and a rolling 12 months calculation for the prior year.

My IF statement says 

NEW CUSTOMER CY = if(and([SALES LY 2023] = 0,[Sales CY 2024] >0), 1, 0)

Which works correctly - but it doesnt total up correctly.

The SALES LY 2023 and SALES CY 2024 are also measures.

Can you please help?

Thanks

Charli

[v-linhuizh-msft]

Hi @Charli2516 ,

Have you solved your problem? If it is solved, please share your solution and accept it as solution or mark the helpful replies, it will be helpful for other members of the community who have similar problems as yours to solve it faster. Thank you very much for your kind cooperation!

Best Regards,
Zhu

[Bibiano_Geraldo]

Hi @Charli2516 ,

please try the bellow measure:

NEW CUSTOMER CY = 
SUMX(
    VALUES(CustomerTable[CustomerID]),
    IF(
        AND(
            [SALES LY 2023] = 0,
            [Sales CY 2024] > 0
        ),
        1,
        0
    )
)

[rajendraongole1]

Hi @Charli2516 - You can achieve this using the SUMX function

create below measure:

NEW CUSTOMER CY =
SUMX(
VALUES(TableName[CustomerID]), -- Replace 'TableName[CustomerID]' with the column that uniquely identifies your customers
IF(
AND([SALES LY 2023] = 0, [SALES CY 2024] > 0),
1,
0
)
)

Hope the above works. please check.

[Charli2516]

i have put the new calculation in the 'Total New Customers' Column, and as you can see this doesnt pull through the same data as the 'New Customer CY'. It does total however..?

[rajendraongole1]

Hi @Charli2516 - can you please modify and update the rows and the total, modify NEW CUSTOMER CY to use the same row-by-row logic applied in Total New Customers. 

NEW CUSTOMER CY =
SUMX(
VALUES('YourTable'[Customer]),
IF(AND([SALES LY 2023] = 0, [Sales CY 2024] > 0), 1, 0)
)

Add a table visual with detailed columns (e.g., Customer, [SALES LY 2023], [Sales CY 2024]).
Verify the row-level logic and ensure [SALES LY 2023] and [Sales CY 2024] return the expected values.

[Charli2516]

I have already done a table to show the logic.

The New customer CY shows that they have sales in 2024 so they should be a 1 for a new customer, but the new formula you have provided doesnt show the 1 in all the customers when it should?

[v-linhuizh-msft]

Thanks for the replies from rajendraongole1 and Bibiano_Geraldo.

Hi @Charli2516 ,

Please try the following formula:

Total New Customer= SUMX('yourtablename',[New Customer CY])

If the problem persists, could you please provide a link to the .pbix file with the sensitive data removed (using e.g. OneDrive, Dropbox, Google Drive), this could help to quickly find the problem!

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.