Sum of a value showing for the first time in the last 12 months

[datatbl123]

I would like a formula that will calculate the count of new billed clients looking at the previously 12-months history of billed clients. Client 3 shows an example of a new client that wasn't billed before so the total count of all clients should be 1. The other clients, when looking at the latest column (December), that have a 1 in the December column look like they have been billed at least more than once in the previous 12 months so these should not be included in the calculation. I am looking for a formula that would use the current month as the month to use to compare to the previous 12 months.

[Jihwan_Kim · Accepted solution]

Hi,

One of ways to solve this is, try to use EXCEPT DAX function in a measure.

EXCEPT function (DAX) - DAX | Microsoft Learn

Please try writing a measure something like similar to below.

new customer list count in current month measure: =
VAR _currentmonthcustomerlist =
    DISTINCT ( Customer[Customer] )
VAR _prevelevenmonthscustomerlist =
    CALCULATETABLE (
        DISTINCT ( Customer[Customer] ),
        DATESBETWEEN (
            Calendar[Date],
            EOMONTH ( MAX ( Calendar[Date] ), -12 ) + 1,
            EOMONTH ( MAX ( Calendar[Date] ), -1 )
        )
    )
RETURN
    COUNTROWS (
        EXCEPT ( _currentmonthcustomerlist, _prevelevenmonthscustomerlist )
    )

[datatbl123]

This worked, thank you!