Forum Discussion
Create Table that Counts Items According to Calendar Dates
Community ChampionNormally I'd lean towards just a measure but does this do what you want for the open monthly:
Open Monthly =
VAR tblOpen =
FILTER (
'Appended Table',
'Appended Table'[Created Date] <= EOMONTH('ADO Metrics'[First of Month], 0 )
&& 'Appended Table'[Created Date] >= 'ADO Metrics'[First of Month]
)
RETURN COUNTROWS ( tblOpen )
If it's what you're looking for I can write a similar expression for closed but it's the same logic.
While that did work to count the number of 'Work Item ID's that were created and closed each month, what I am trying to do is understand how many are opened and remain open each month.
For example, when the system was launched our first 'Work Item ID' was recorded on 9/17/2017 so the data is showing "1" for the month of 9/17. However, when you look at the next month on the table (10/17) there is no data. It should still be counting that the 'Work Item ID' as open, because it does not yet have a "Closed Date".
For example let's say I have four projects with following open/closed dates:
#1 Opened 9/1/2017 - Closed 11/1/2017
#2 Opened 10/1/2017 - Closed 11/1/2017
#3 Opened 10/1/2017 - NOT CLOSED
#4 Opened 12/1/2017 - NOT CLOSED
I'd expect to see
First of Month OpenMonthly 1/1/2017 0 2/1/2017 0 3/1/2017 0 4/1/2017 0 5/1/2017 0 6/1/2017 0 7/1/2017 0 8/1/2017 0 9/1/2017 1 10/1/2017 3 11/1/2017 1 12/1/2017 2 I hope that makes sense!
Thanks!- bcdobbs
No worries what about this:
Open Monthly = VAR tblOpen = FILTER ( 'Appended Table', 'Appended Table'[Created Date] <= 'ADO Metrics'[First of Month] && ( 'Appended Table'[Closed Date] > EOMONTH ( 'ADO Metrics'[First of Month], 0 ) || ISBLANK ( 'Appended Table'[Closed Date] ) ) ) RETURN COUNTROWS ( tblOpen ) + 0It still isn't counting how I would expect it to. I uploaded a file to my Google Drive that shows three things:
1. An actual sample of the data from 1/1/17 - 12/31/19 (no sensitive or identifying data is present)
2. A recreation of the table/column that the code you provided is creating based on my sample data
3. A manual creation of what I would expect to see.
You will see highlights in the sample data, I simply did this to easily identify and count the Work Item IDs that had closed dates in November and December of 2019, as the system had really taken off during that time period.
The overall goal is to be able to trend how many remain open and unresolved monthly. For example:
If project 1 was opened on 1/1/22 and closed that same month, it wouldn't be reflected in the table.
If project 2 was opened 1/1/22 and closed on 2/1/22, it would be reflected in the table as "1" in January, but nothing in February.
Building on the above, if project 3 was opened 1/1/22 and had not yet been as of the end of February, I would expect the table to reflect "2" in January and "1" in February and then drop to zero in the month project 3 is finally completed.
Perhaps there is a much easier way to do this and keep a historical record, but I am super new to all this.
Thanks!