DAX to count how many tasks have not completed in time.

[byebytoad9766]

Hi,

I'm currently working on constructing a line chart to illustrate tasks/projects that were not completed within the expected timeframe.

We have four distinct types of tasks: Support call, Task, Assignment, and Project. Each of these tasks has its own predefined time threshold for completion, which are 3, 14, 90, and 180 days respectively.

My aim is to depict, using the first day of each month as a reference point, the combined count of tasks that were still open on that day and exceeded the allotted time, along with tasks that were closed in the previous month but were closed beyond the expected timeframe.

Please find below a sample table as an example:

ID	Name	Opening Date	Completion Date	Task Type
PT00001	Name 00001	24/08/2023	01/02/2024	Project
PT00002	Name 00002	11/09/2023	28/09/2023	Task		Project	180 days
PT00003	Name 00003	22/09/2023		Assignment		Task	14 days
PT00004	Name 00004	13/10/2023	23/10/2023	Support Ticket		Assignment	90 days
PT00005	Name 00005	11/09/2023	09/01/2024	Project		Support Ticket	3 days
PT00006	Name 00006	06/10/2023	18/10/2024	Task
PT00007	Name 00007	19/10/2023	02/02/2024	Assignment
PT00008	Name 00008	16/11/2023	20/11/2023	Support Ticket
PT00009	Name 00009	17/11/2023		Project
PT00010	Name 00010	20/11/2023	21/11/2023	Support Ticket
PT00011	Name 00011	21/11/2023	01/12/2023	Support Ticket
PT00012	Name 00012	22/11/2023	22/11/2023	Support Ticket
PT00013	Name 00013	09/12/2023		Project
PT00014	Name 00014	24/12/2023	09/01/2024	Task
PT00015	Name 00015	18/01/2024		Assignment
PT00016	Name 00016	20/01/2024	28/01/2024	Support Ticket
PT00017	Name 00017	10/01/2024	20/01/2024	Support Ticket
PT00018	Name 00018	31/01/2024	11/04/2024	Task
PT00019	Name 00019	22/02/2024		Assignment

If the DAX is correct, it should count as follows: 

					Total
Sept	ID 02 (late completion within the month)				1
Oct	ID04 (late completion within the month)				1
Nov	ID08 (late completion within the month)	ID08 (late completion )			2
Dec					0
Jan	ID 003 (Assignment went over the threshold during Jan)			1
Feb	ID 014 (Task went over the threshold during Jan)	ID 003 (Assignment went over the threshold during Jan)	ID 016 (late completion within the month)	ID 017 (late completion within the month)	4

[Anonymous · Accepted solution]

Hi @byebytoad9766 ,

The table  data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a column named ‘Spend Time’

Spend Time = IF(ISBLANK('Tabelle1'[Completion Date]),0,DATEDIFF('Tabelle1'[Opening Date],'Tabelle1'[Completion Date],DAY)) 

2. Use the following DAX expression to create a column named ‘Deadline’

Deadline = IF('Tabelle1'[Spend Time] <> 0,
BLANK(),
SWITCH(TRUE(), 
'Tabelle1'[Task Type] = "Project",'Tabelle1'[Opening Date] + 180,
'Tabelle1'[Task Type] = "Task",'Tabelle1'[Opening Date] + 14,
'Tabelle1'[Task Type] = "Assignment",'Tabelle1'[Opening Date] + 90,
'Tabelle1'[Task Type] = "Support Ticket",'Tabelle1'[Opening Date] + 3))

3. Use the following DAX expression to create a column named ‘CheckMonth’

CheckMonth = IF('Tabelle1'[Spend Time] <> 0 ,
IF('Tabelle1'[Completion Date].[MonthNo] = 12,
DATE('Tabelle1'[Completion Date].[Year] + 1,1,1),
DATE('Tabelle1'[Completion Date].[Year],'Tabelle1'[Completion Date].[MonthNo] +1 ,1)),
IF('Tabelle1'[Deadline].[Day] = 1,[Deadline],
IF('Tabelle1'[Deadline].[MonthNo] = 12,
DATE('Tabelle1'[Deadline].[Year] + 1,1,1),
DATE('Tabelle1'[Deadline].[Year],'Tabelle1'[Deadline].[MonthNo] +1 ,1))))

4. Use the following DAX expression to create a column named ‘Whether it timed out’

Whether it timed out = IF('Tabelle1'[Spend Time] <> 0,
SWITCH(TRUE(),
'Tabelle1'[Task Type] = "Project" && 'Tabelle1'[Spend Time] <= 180 ,"NO",
'Tabelle1'[Task Type] = "Task" && 'Tabelle1'[Spend Time] <= 14 ,"NO",
'Tabelle1'[Task Type] = "Assignment" && 'Tabelle1'[Spend Time] <= 90 ,"NO",
'Tabelle1'[Task Type] = "Support Ticket" && 'Tabelle1'[Spend Time] <= 3 ,"NO",
"YES"
),
"YES")

5. Use the following DAX expression to create a measure named ‘Timeout’

Timeout = COUNTROWS(FILTER('Tabelle1','Tabelle1'[Whether it timed out] = "YES"))

6. Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[123abc]

To construct a DAX (Data Analysis Expressions) formula to count tasks that were not completed within the expected timeframe, you can follow these steps:

1. Create a calculated column for each task to calculate the expected completion date based on its type.
2. Use DAX measures to count tasks that were open on the first day of each month and exceeded the allotted time, along with tasks closed in the previous month but beyond the expected timeframe.

Let's break down each step:

1. Create calculated columns:
   • You need to calculate the expected completion date based on the task type. You can create a calculated column for each task type. Here's how you can calculate the expected completion date for each task type:

Expected_Completion_Date =
SWITCH (
'SampleTable'[Task Type],
"Project", 'SampleTable'[Opening Date] + 180,
"Assignment", 'SampleTable'[Opening Date] + 14,
"Task", 'SampleTable'[Opening Date] + 14,
"Support Ticket", 'SampleTable'[Opening Date] + 3,
BLANK ()
)

Create DAX measures:

• You need to count tasks that were still open on the first day of each month and exceeded the allotted time, along with tasks closed in the previous month but were closed beyond the expected timeframe.

Tasks_Not_Completed_On_Time =
VAR CurrentMonth = MONTH ( TODAY () )
VAR CurrentYear = YEAR ( TODAY () )
RETURN
CALCULATE (
COUNTROWS ( 'SampleTable' ),
FILTER (
'SampleTable',
(
YEAR ( 'SampleTable'[Completion Date] ) = CurrentYear &&
MONTH ( 'SampleTable'[Completion Date] ) = CurrentMonth - 1 &&
'SampleTable'[Completion Date] > 'SampleTable'[Expected_Completion_Date]
) ||
(
ISBLANK ( 'SampleTable'[Completion Date] ) &&
YEAR ( 'SampleTable'[Opening Date] ) = CurrentYear &&
MONTH ( 'SampleTable'[Opening Date] ) = CurrentMonth &&
TODAY () > 'SampleTable'[Expected_Completion_Date]
)
)
)

Tasks_Not_Completed_On_Time =
VAR CurrentMonth = MONTH ( TODAY () )
VAR CurrentYear = YEAR ( TODAY () )
RETURN
CALCULATE (
COUNTROWS ( 'SampleTable' ),
FILTER (
'SampleTable',
(
YEAR ( 'SampleTable'[Completion Date] ) = CurrentYear &&
MONTH ( 'SampleTable'[Completion Date] ) = CurrentMonth - 1 &&
'SampleTable'[Completion Date] > 'SampleTable'[Expected_Completion_Date]
) ||
(
ISBLANK ( 'SampleTable'[Completion Date] ) &&
YEAR ( 'SampleTable'[Opening Date] ) = CurrentYear &&
MONTH ( 'SampleTable'[Opening Date] ) = CurrentMonth &&
TODAY () > 'SampleTable'[Expected_Completion_Date]
)
)
)

This measure calculates the count of tasks meeting the specified conditions for each month. It counts tasks closed in the previous month but beyond the expected timeframe, as well as tasks that are still open on the first day of the current month and exceeded the allotted time.

Ensure to replace 'SampleTable' with the actual name of your table in Power BI or whichever tool you're using. Also, ensure that the column names in the formulas match the column names in your dataset.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.