COUNTIF Across 2 Tables in PowerBI

[Anonymous]

Hi,

I am trying to recreate some tables I originally had made in Excel. The goal is to count the number of times an item is completed AFTER a set week. The formula to accomplish this in excel was: 

=COUNTIFS(Table1[[Due_Date]:[Due_Date]],">="&D$2,Table1[[Due_Date]:[Due_Date]],"<="&D$3,Table1[[Priority]:[Priority]],"low",Table1[[Complete_Date]:[Complete_Date]],">="&E$2)

My issue with PowerBI is that my set weeks are populated in one table (Table1- attached below)

While the items I am comparing them to are located in a much larger table (>7,000 rows) (Table2 - attached below)

I am essentially trying to tag the "DueDate" of an item (Second PowerBI table) to a set "ProdWeekNum" (first PowerBI table), and then determine if the item (Second PowerBI table) had a "Complete Date" after that set week (to be used to measure schedule compliance). 

Excel formula deconstructed roughly:

=COUNTIFS(Table1[[Due_Date]:[Due_Date]],">="&D$2,Table1[[Due_Date]:[Due_Date]],"<="&D$3,

- Ensures that an item has a due date within a specified set week

Table1[[Priority]:[Priority]],"low",

- Finds only items with a Priority set to low (this part is easily handled in PowerBI (slicer, filter, etc.), no need to add this into the DAX coding)

Table1[[Complete_Date]:[Complete_Date]],">="&E$2)

- Counts the item if the completion date was after the specified set week that the due date occured in

Excel Table

PowerBI - Table1

PowerBI - Table2

Appreciate any help/resources that can be provided.

Kind regards,

MH

[amitchandak]

@Anonymous ,Not very clear. Can you share sample data and sample output.

See if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

[Anonymous]

@amitchandak 

I believe an expression similar to the following is what I am looking for, however it is throwing up an error stating that the minimum argument count is 2:

CompleteTasks = calculate(COUNTX(filter(Tasks, Tasks[Due Date] <= Max(PoCCalendar[Date]) && Tasks[Due Date] >= Min(PoCCalendar[Date]) && Tasks[Complete Date] >= Max(PoCCalendar[Date])))) 

Kind regards,

MH 

[amitchandak]

You missed parameter for countx like Tasks[ID]

CompleteTasks = calculate(COUNTX(filter(Tasks, Tasks[Due Date] <= Max(PoCCalendar[Date])
&& Tasks[Due Date] >= Min(PoCCalendar[Date]) && Tasks[Complete Date] >= Max(PoCCalendar[Date])),Tasks[ID]))

[Anonymous]

@amitchandak 

I essentially copied this expression from your tutorial:

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

 What does the CROSSFILTER function do in this specific case? 

I haven't checked if this solution works yet. Going to compare some data to ensure this is the proper solution. 

[Anonymous]

@amitchandak I appreciate the help you've given thus far. I have managed to determine the # of tasks completed late each week as well as the # of tasks with statuses not set to completed each week.

TasksCompletedLate = calculate(COUNTX(filter(Tasks, Tasks[Due Date] <= Max(PoCCalendar[Date])
&& Tasks[Due Date] >= Min(PoCCalendar[Date]) && Tasks[Complete Date] >= Max(PoCCalendar[Date]) && Tasks[Status] = "Completed"),Tasks[OFNumber]))

TasksNotCompleted = calculate(COUNTX(filter(Tasks, Tasks[Due Date] <= Max(PoCCalendar[Date])
&& Tasks[Due Date] >= Min(PoCCalendar[Date]) && Tasks[Status] <> "Completed"),Tasks[OFNumber]))

However, the tasks completed late table is not summing for some reason:

Any suggestions??

[amitchandak]

@Anonymous , try this measure and check

TasksCompletedLate new=
sumX(summarize(Tasks,Tasks[OFNumber],"_1",[TasksCompletedLate]),[_1])

If works, seem like row context issue

[Anonymous]

Hey @amitchandak 

My apologies that my request was not very clear. I hope the following helps.

I have one table with a set of "task" data. It includes information for over 7000 tasks such as task ID, description, due date, completion date. 

I have a second table that has our companies set production weeks (wednesday to tuesday). 

This is to be used to determine schedule compliance which means that whatever set production week the due date falls into, the completion date must also occur within that set production week. 

I want to create a DAX expression that compares the due date of a task to a set production week, and then determines if the completion date was within that set production week or if it was after the set production week. 

An example to illustrate what I am attempting to solve:

Production Week 1:

Jan 1st 2020 to Jan 7th 2020 (Wednesday to Tuesday)

Task 1:

Due Date: Jan 3rd, 2020

Completion Date: Jan 4th, 2020

Task 2: 

Due Date: Jan 4th, 2020

Completion Date: Jan 10th, 2020

In the above example, the expression that I am wanting to create would not count Task 1 as its completion date fell within the set production week. However, it would count Task 2 as it's completion date occured after the set production week.

Please let me know if this is making sense.

Kind regards,

MH