Forum Discussion

ArulBhargavR's avatar
ArulBhargavR
Frequent Visitor
2 years ago
Solved

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

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 usi...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

ArulBhargavR's avatar
ArulBhargavR
Frequent Visitor
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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