Count rows where calendar date is between row posting date and row completed date

[phill2cb]

I am working on an inventory dashboard and I have a table that lists all lots and their Receipt Inspection status. When a material is received, an Inspection Lot is created and is either Approved or Rejected. If it hasnt been inspected yet, that field is null. I have two date fields in the table, one that lists when the PO was received (or you can think of it as Lot creation date) and the other that lists when the approve/reject decision was made (null if not inspected yet). Below is a sample of what this looks like

Inspection Lot		Material		Inspection code		Inspector		Inspection Date		Purchase Order		PO Posting Date
10000072249		100328281								4701458624		1/21/2021
10000072237		100290069								4701521327		1/20/2021
10000072246		100326965								4701521328		1/20/2021
10000072601		100304853								4701523011		1/19/2021
10000072598		100280123		A		U28211		1/19/2021		4701523285		1/19/2021
10000072549		100331753								4701368917		1/15/2021
10000072343		600000003		A		U22246		1/15/2021		4701510516		1/14/2021
10000072577		100016556		A		U28211		1/14/2021		4701519651		1/14/2021
10000072334		600000952		A		U22246		1/15/2021		4701507519		1/14/2021
10000072341		600000930		A		U22246		1/15/2021		4701510516		1/14/2021
10000072344		600000162		A		U22246		1/15/2021		4701510516		1/14/2021
10000072332		600000116		A		U22246		1/15/2021		4701501036		1/14/2021
10000072342		600000003		A		U22246		1/15/2021		4701510516		1/14/2021
10000072576		100064271		A		U28211		1/14/2021		4701519651		1/14/2021
10000072581		100045788		A		U28211		1/14/2021		4701519501		1/14/2021
10000072573		100320267		A		U28211		1/14/2021		4701518593		1/14/2021
10000072340		600000930		A		U22246		1/15/2021		4701510516		1/14/2021
10000072227		100005046		A		U28211		1/15/2021		4701519571		1/12/2021

My goal is to get a historical count of whats pending inspection so our Quality Inspection team can see if their backlog is going up or down. The data above is a small sample and the actual data is 27K lines going back to 2007. In the above table, the last Lot, 10000072227, was received 1/12/21 and inspected on 1/15/21 so it should be counted as pending on 1/12, 1/13, and 1/14.

In Excel, I pending inspection count formula as below where A2 is the date in the table below. For each day, it counts any Inspection Lot that was received on or before that date and removes any Lots that were inspected on or before that date.

=COUNTIF(Table1[PO Posting Date],"<="&A2)-COUNTIF(Table1[Inspection Date],"<="&A2)

Date		Pending Count
1/12/2021		1
1/13/2021		1
1/14/2021		8
1/15/2021		1
1/16/2021		1
1/17/2021		1
1/18/2021		1
1/19/2021		2
1/20/2021		4
1/21/2021		5

In Power BI, I would like a count for each day in the calendar of what had not been inspected yet.

[danielgajohnson]

@HotChilli I'm trying to do something similar and can't get this to work. I've got a projects table that has a start date and an end date and I'm looking to get the total number of projects in progress during each week/month/quarter. Tell me if my thinking is off, the idea is to take the date from the calendar table and ask the projects table to count every row where the Calendar[Date] is between (in my case) Projects Lookup Table[Date Created] and Projects Lookup Table[Complete Date]. Do I have that right? Here's what I've got:

In Progress Count = VAR _date = SELECTEDVALUE('Calendar'[Date])
RETURN
    COUNTROWS(
    FILTER('Projects Lookup Table', 'Projects Lookup Table'[Date Created] <= _date &&
    'Projects Lookup Table'[Complete Date]> _date)
    )

It's not returning any values when Calendar[Date] is on the Axis of a line chart. 

It probably needs to include more conditions. For example, current in progress projects have blanks in the complete date column. There is a status column.

I fee like I'm missing something in the filter logic. What I really want is to count the rows where _date is between Projects Lookup Table'[Date Created] and Projects Lookup Table'[Complete Date] if Projects Lookup Table'[Status] = "Completed", otherwise count the rows where Projects Lookup Table'[Status] = "In Progress" and _date >= Projects Lookup Table'[Date Created]. Is that possible? What am I missing?

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[danielgajohnson]

The projects table has dozens of rows, but here are the ones I'm interested in:

Project Name		Date Create		Status		Complete Date
Project D		8/31/21		In Progress
Project C		8/28/21		Complete		9/2/21
Project B		8/26/21		Complete		9/1/21
Project A		8/25/21		In Progress

The calendar table is a standard calendar table with columns like Date (starts 1/1/12), Month-Year, Quarter-Year, Start of Week and so on.

Here's what a table output might look like for August:

Date		Count of In Progress Projects
8/24/21		0
8/25/21		1
8/26/21		2
8/27/21		2
8/28/21		3
8/29/21		3
8/30/21		3
8/31/21		4
9/1/21		3
9/2/21		2

And so on.

Possible?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[danielgajohnson]

Thank you @Ashish_Mathur that worked! I had to make some additional modifications to the query to filter out some other scenarios, but it all looks good now.

Thanks!

[Ashish_Mathur]

You are welcome.  If my previous reply helped, please mark it as Answer.

[phill2cb]

Apologies for the table formatting, I'm not sure why its reformatting like that after I post it.

[HotChilli · Accepted solution]

If you create a date table (a single column of dates).  Then pull the date column on to a table vis.

Create a measure similar to this:

MeasureY = VAR _date = SELECTEDVALUE(Dates[Date])
RETURN
    COUNTROWS(
    FILTER(Table1,
        Table1[PO Posting Date] <= _date  &&
        (ISBLANK(Table1[Inspection Date]) || Table1[Inspection Date] > _date))
    )

and put that on to the table vis.

Depending on the values in the date table, you'll get repeating values from the end of the valid dates so you might want to limit the last date in the table.

Please test at your side.

[phill2cb]

Thank you, this worked great! It did have the repeating values but I just added a filter to the date that removes date after today.