Return the number of instance a boolean value is repeating per category

[ArulBhargavR]

Hi, I have sales data with invoice date, document number and if the invoice is outstanding, I would want to return the instance number of the outstanding invoice for each customer in a new coulmn using DAX. I have attached the data sample and expected result

Sample Data : 

Result :

[ArulBhargavR]

Hi @Anonymous, 

Thank you for showing intrest, I have tried the solution presented earlier..after adding customer to the filter function, I have been able to get the total count of "Yes" for said category.

however, I want to identify the first instance of "Yes" for each customer based on the earliest date.

[Anonymous · Accepted solution]

Hi @ArulBhargavR,

So you mean you only want the first match condition record return 1 and other records return 0? If that is the case , you can try to use the following measure formula and I add variable and condition to exclude not match scenarios:

Invoice Count =
VAR currDate =
    MAX ( Calendar[Date] )
VAR _prevCount =
    CALCULATE (
        COUNT ( Invoice[posting date] ),
        FILTER (
            ALLSELECTED ( Invoice ),
            [posting date] < currDate
                && [outstanding invoice] = "Yes"
        ),
        VALUES ( Invoice[customer] )
    )
RETURN
    IF (
        _prevCount = 0,
        CALCULATE (
            COUNT ( Invoice[document number] ),
            FILTER (
                ALLSELECTED ( Invoice ),
                [posting date] <= currDate
                    && [outstanding invoice] = "Yes"
            ),
            VALUES ( Invoice[customer] )
        )
    ) + 0

Regards,

Xiaoxin Sheng

[Anonymous]

HI @ArulBhargavR,

You can use the following measure formula to get the outstanding invoice count based on 'customer' and 'posting date' group:

Invoice Count =
VAR currDate =
    MAX ( Calendar[Date] )
RETURN
    CALCULATE (
        COUNT ( Invoice[document number] ),
        FILTER (
            ALLSELECTED ( Invoice ),
            [posting date] <= currDate
                && [outstanding invoice] = "Yes"
        ),
        VALUES ( Invoice[customer] )
    )+0

Regards,
Xiaoxin Sheng