Create Table that Counts Items According to Calendar Dates

Question

My situation is the following:&nbsp;I have a main data table that looks like this:Appended TableWork Item IDCreated DateClosed Date11/1/20214/1/202123/1/2021&nbsp;35/1/20216/1/202146/1/20217/1/2021512/1/2021&nbsp;&nbsp;I also created a calendar table using this code:&nbsp;Calendar = GENERATE ( CALENDAR(DATE ( YEAR ( MIN ( 'Appended Table'[Created Date]) ), 1, 1 ),DATE ( YEAR ( TODAY() ), 12, 1 )),VAR currentDay = [Date]VAR month = MONTH ( currentDay )VAR year = YEAR ( currentDay )RETURN ROW ( "First of Month", DATE(year,month,01) ))&nbsp;Lastly I have a third table, "ADO Metrics"&nbsp;that was created using the following code:&nbsp;ADO Metrics = values('Calendar'[First of Month])&nbsp;What I am trying to do is create two more columns on this table that will count how many unique 'Work Item ID' from the "Appended Table" were open and closed during each one month period. The expected result would look something like this:&nbsp;ADO Metrics First of MonthOpenMonthly&nbsp;ClosedMonthly1/1/2021102/1/2021103/1/2021204/1/2021015/1/2021206/1/2021217/1/2021118/1/2021109/1/20211010/1/20211011/1/20211012/1/202120&nbsp;The goal is to be able to create simple charts showing how many projects are opened a closed monthly. Could someone help with what code I need to write to reach the result in the table above?

bcdobbs · Answer

Normally I'd lean towards just a measure but does this do what you want for the open monthly:&nbsp;Open Monthly = 
VAR tblOpen = 
    FILTER (
        'Appended Table',
        'Appended Table'[Created Date] &lt;= EOMONTH('ADO Metrics'[First of Month], 0 )
        &amp;&amp; 'Appended Table'[Created Date] &gt;=  '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.&nbsp;

anonymous · Answer

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 · Answer

No worries what about this:Open Monthly = 
VAR tblOpen = 
    FILTER (
        'Appended Table',
        'Appended Table'[Created Date] &lt;=  'ADO Metrics'[First of Month]
        &amp;&amp; (
            'Appended Table'[Closed Date] &gt; EOMONTH ( 'ADO Metrics'[First of Month], 0 ) 
            || ISBLANK ( 'Appended Table'[Closed Date] ) 
            )
    )
RETURN COUNTROWS ( tblOpen ) + 0

anonymous · Answer

It still isn't counting how I would expect it to. I uploaded a file to my Google Drive that shows three things:&nbsp;https://docs.google.com/spreadsheets/d/1UU1zocRw21igRtju83O5o-BSE1XqvLvR/edit?usp=sharing&amp;ouid=105953713352020781370&amp;rtpof=true&amp;sd=true&nbsp;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 data3. A manual creation of what I would expect to see.&nbsp;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.&nbsp;&nbsp;The overall goal is to be able to trend how many remain open and unresolved monthly. For example:&nbsp;If project 1 was opened on 1/1/22 and closed that same month, it wouldn't be reflected in the table.&nbsp;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.&nbsp;&nbsp;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.&nbsp;Perhaps there is a much easier way to do this and keep a historical record, but I am super new to all this.&nbsp;Thanks!

Appended Table
Work Item ID	Created Date	Closed Date
1	1/1/2021	4/1/2021
2	3/1/2021
3	5/1/2021	6/1/2021
4	6/1/2021	7/1/2021
5	12/1/2021

First of Month	OpenMonthly	ClosedMonthly
1/1/2021	1	0
2/1/2021	1	0
3/1/2021	2	0
4/1/2021	0	1
5/1/2021	2	0
6/1/2021	2	1
7/1/2021	1	1
8/1/2021	1	0
9/1/2021	1	0
10/1/2021	1	0
11/1/2021	1	0
12/1/2021	2	0

Forum Discussion

Create Table that Counts Items According to Calendar Dates

4 Replies

Related content

Create a Calendar Year Visual

Calendar Slicer with Different Business Calendars

Custom calendar

Tweaking Financial Calendar

Create weekly Calendar table