Forum Discussion

Elisa112's avatar
Elisa112
Helper V
1 year ago
Solved

Find overdue dates this month

Hello   I am trying to create a column/measure to count the current months overdue dates per customer, here is an example of my data and expected outcome column (Overdue)   CustomerID   ActivityI...
Need Help
  • anilelmastasi's avatar
    anilelmastasi
    1 year ago
    Overdue_Flag =
IF(
AND(
YourTable[Complete By] <= EOMONTH(TODAY(), 0), 
YourTable[Complete By] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) 
),
1,
0
)

     

    Can you please try this as calculated column?

  • anilelmastasi's avatar
    anilelmastasi
    1 year ago

    This measure is currently counting all records, not just those where DaysOverdueFlag equals 1.

    This behavior occurs because the expression myTable[DaysOverdueFlag] = 1 evaluates to a boolean TRUE or FALSE for each row, and COUNTAX counts all non-blank results, including FALSE values.

     

    To count only the rows where DaysOverdueFlag equals 1, you can use the CALCULATE function in combination with the COUNTROWS function and a filter condition:

     

    Overdue Flag = CALCULATE(

        COUNTROWS(myTable),

        myTable[DaysOverdueFlag] = 1

    )

anilelmastasi's avatar
anilelmastasi
Super User
1 year ago

This measure is currently counting all records, not just those where DaysOverdueFlag equals 1.

This behavior occurs because the expression myTable[DaysOverdueFlag] = 1 evaluates to a boolean TRUE or FALSE for each row, and COUNTAX counts all non-blank results, including FALSE values.

 

To count only the rows where DaysOverdueFlag equals 1, you can use the CALCULATE function in combination with the COUNTROWS function and a filter condition:

 

Overdue Flag = CALCULATE(

    COUNTROWS(myTable),

    myTable[DaysOverdueFlag] = 1

)

Elisa112's avatar
Elisa112
Helper V
1 year ago

yes that also works 

Thanks for all your help much appreciated