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 using DAX. I have attached the data sample and expected result

 

Sample Data : 

 

 

Result :

 

 

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

3 Replies

  • 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

  • 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