Number of rows of table where the date from another table is between two dates of the first table

[Anonymous]

Hello, 

I've been struggling with Power Bi for some time now and I can't seem to find the answer to my question here. 

I hope that I am posting at the right place.

I have one table with dates (each month from 2020 to 2022) and another table with information on some tickets (three dates for each problem : creation of the ticket, resolution of the problem and closure of the ticket). 

I would like to have for each date of the first table the number of tickets currently open but not resolved yet, the number of tickets resolved but not closed and the number of tickets closed. To do that, I wanted to compare the dates so that I could count the rows where table1[date] is between table2[creation date] and table2[resolution date] for example but I don't know how to do that since I can't have an active 1-to-1 relationship between table1[date] and the three dates of the table2. 

All data is sorted by project (there is a relationship on the Project Name column between the two tables and each month appears multiple times in each table) and I actually have a lot of other columns on table2, the idea would be to keep the information of table2 so that if I click on one month from table 1, I would be able to retrieve for example the list of tickets id that were already created but still not resolved on that month. I don't know if this is possible or if I have to change the tables to retrieve that.

Table 1 would be something like that (and I would like to add the columns with the number of tickets open etc)

Project name	Date
Project1	01/01/2020
Project1	01/02/2020
Project1	01/03/2020
Project1	01/04/2020
Project1	01/05/2020
Project1	01/06/2020
Project1	01/07/2020
Project1	01/08/2020
Project1	01/09/2020
Project1	01/10/2020
Project1	01/11/2020
Project1	01/12/2020
Project1	01/01/2021
Project1	01/02/2021
Project1	01/03/2021
Project1	01/04/2021
Project1	01/05/2021
Project1	01/06/2021
Project1	01/07/2021
Project1	01/08/2021
Project1	01/09/2021
Project1	01/10/2021
Project1	01/11/2021
Project1	01/12/2021
Project1	01/01/2022
Project1	01/02/2022
Project1	01/03/2022

And Table 2:

Project name	Ticket ID	Month creation	Month resolution	Month closure
Project1	id1	01/01/2020	01/11/2020	01/01/2021
Project1	id2	01/04/2020	01/03/2021
Project1	id3	01/01/2021	01/02/2022
Project1	id4	01/11/2020	01/04/2021
Project2	id5	01/03/2021	01/12/2021
Project2	id6	01/01/2022
Project2	id7	01/09/2021
Project3	id8	01/09/2020	01/11/2021	01/11/2021

Thank you a lot,

Julianna

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Column:

open but not resolved =
CALCULATE (
    COUNT ( Table2[Project name] ),
    FILTER (
        Table2,
        [Month creation] <= EARLIER ( Table1[Date] )
            && [Month resolution] > EARLIER ( Table1[Date] )
            && [Project name] = EARLIER ( Table1[Project name] )
    )
)

resolved but not closed =
CALCULATE (
    COUNT ( Table2[Project name] ),
    FILTER (
        Table2,
        [Month resolution] <= EARLIER ( Table1[Date] )
            && [Month closure] > EARLIER ( Table1[Date] )
            && [Project name] = EARLIER ( Table1[Project name] )
    )
)

closed =
CALCULATE (
    COUNT ( Table2[Project name] ),
    FILTER (
        Table2,
        [Month closure] <= EARLIER ( Table1[Date] )
            && [Project name] = EARLIER ( Table1[Project name] )
            && [Month closure] <> BLANK ()
    )
)

This chart shows the number of tickets in different states for each date.

Regarding the issue of clicking on the month in Table 1 to derive the ID in Table 2, you can do this.

Measure:

Measure =
IF (
    SELECTEDVALUE ( Table1[Date] ) >= SELECTEDVALUE ( Table2[Month creation] )
        && SELECTEDVALUE ( Table1[Date] ) < SELECTEDVALUE ( Table2[Month resolution] ),
    1,
    0
)

Put this Measure into the filter and set it to equal 1.

 

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous ,Based on what I got

new column in table 1
= countx(filter(Table2, Table1[Project name] = Table2[Project name] && Table2[Month creation] <= Table1[Date] && Table2[Month resolution] >= Table1[Date]), Table2[Project Name])

[Anonymous]

Thank you a lot for your answer. I did what you said but my problem now is that I can't link the number I get to the tickets id from the table 2.

Here is a screenshot of what I get using what you said on the example of data I gave:

You can see that the list of tickets id is not linked to the date of table 1, is there another way I could do that in order to have only the list of the 3 ids for this month? 

I know I could create a column in Table1 with the same filter that you gave in order to have that list but the thing is that I actually have a lot of different values in my data for Table2 (and not only that list of ids) and I would like to be able to select one month of Table1, and see all the data from Table2 such that the tickets that are concerned have already been open but are not yet resolved or closed. Do you think there is a way to do that?