Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Create Table that Counts Items According to Calendar Dates

My situation is the following:   I have a main data table that looks like this: Appended Table Work Item ID Created Date Closed Date 1 1/1/2021 4/1/2021 2 3/1/2021   3 5/1/20...
Need Help
bcdobbs's avatar
bcdobbs
Icon for Community Champion rankCommunity Champion
4 years ago

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 ) + 0
Anonymous's avatar
Anonymous
Not applicable
4 years ago

It still isn't counting how I would expect it to. I uploaded a file to my Google Drive that shows three things:

 

https://docs.google.com/spreadsheets/d/1UU1zocRw21igRtju83O5o-BSE1XqvLvR/edit?usp=sharing&ouid=105953713352020781370&rtpof=true&sd=true

 

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!