Forum Discussion
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 ActivityID Status Start Date Complete By Overdue
001 002 In Progress 1 Jan 31 Jan
001 004 Completed 2 Jan 2 Feb 1
Thank you in advance
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?
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
)
12 Replies
Hi Elisa112 ,
Can you try the below code please:
Overdue = VAR CurrentMonthStart = DATE(YEAR(TODAY()), MONTH(TODAY()), 1) VAR CurrentMonthEnd = EOMONTH(CurrentMonthStart, 0) RETURN CALCULATE( COUNTROWS(YourTable), YourTable[Complete By] < CurrentMonthStart )If this helps, please accept a solution.
Thank you!
I tried this as a measure and calculated column and it is giving me 1 for every record, rather than just the overdue (current month) count
By mentioning current month,
Do you mean Complete By column's month or dynamically changing current month?
