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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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