Forum Discussion
Helper IIIMy brain doesn't function, help! Count overdues in a month.
Calculated Column
OpenAndOverdueCount =
VAR StartDate = Report[First day]
VAR EndDate = Report[Last day]
RETURN
COUNTROWS(
FILTER(
IW38,
IW38[Date created] <= EndDate &&
(ISBLANK(IW38[Date finished]) || IW38[Date finished] >= StartDate) &&
IW38[Duedate] < EndDate
)
)💌If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
Super UserHi IAM
Try this step-by-step approach to achieve this:
Create a Calculated Column for Overdue Tickets: First, you need to determine if a ticket is overdue. You can create a calculated column in your IW38 table to check if the ticket is overdue.
IW38[IsOverdue] = IF(IW38[Date finished] > IW38[Duedate], 1, 0)
Create a Calculated Column for Open Tickets: Next, you need to determine if a ticket was open during the month. You can create a calculated column in your IW38 table to check if the ticket was open during the month.
IW38[IsOpenDuringMonth] =
IF(
AND(
IW38[Date created] <= Report[Last day],
OR(
ISBLANK(IW38[Date finished]),
IW38[Date finished] >= Report[First day]
)
),
1,
0
)
Count Overdue and Open Tickets: Now, you can create a calculated column in your Report table to count the number of tickets that were both open and overdue during the month.
Report[OverdueOpenTickets] =
CALCULATE(
COUNTROWS(IW38),
FILTER(
IW38,
IW38[IsOverdue] = 1 &&
IW38[IsOpenDuringMonth] = 1 &&
IW38[Date created] <= Report[Last day] &&
(ISBLANK(IW38[Date finished]) || IW38[Date finished] >= Report[First day])
)
)
This approach should help you count the number of tickets that were open and overdue during the specified month. Make sure to adjust the column names and table names according to your actual data model.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!