Filter based on date range in another table

[Anonymous]

I have a table of data that looks something like this:

Bug Title	Created Date	Closed Date	Project
Bug 1	1-1-2000	1-30-2000	Project 1
Bug 2	1-5-2000	1-25-2000	Project 1
Bug 3	1-1-2000	2-10-2000	Project 1
Bug 4	1-2-2000	1-30-2000	Project 2
Bug 5	1-1-2000	1-30-2000	Project 2

Then I have another table that keeps track of Project data:

Milestone	Project	Milestone Date
Milestone 1	Project 1	12-31-1999
Milestone 2	Project 1	1-15-2000
Milestone 3	Project 1	1-30-2000

I've created a table containing all dates in a certain range in order to be able to form the relationships between these 2 sets of data.

What I'm trying to do is allow the user to filter by project and then create a line chart that counts the number of bugs that were active on a certain date. So for the sample data, for Project 1, the count should be 2 from 1-1 to 1-4 then 3 from 1-5 to 1-25, 2 from 1-26 to 1-30, and 1 from 1-31 to 2-10. How do I go about counting this over the date range?

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can create a calculated table contain all the possible date first (ignore this step if you already have such one):

DateTable =
CALENDAR ( MIN ( 'Table'[Created Date] ), MAX ( 'Table'[Closed Date] ) )

Then we create a measure to count the number:

BugsCount = 
SUMX (
    'Table',
    COUNTX (
        FILTERS ( 'DateTable'[Date] ),
        IF (
            [Date] >= 'Table'[Created Date]
                && [Date] <= 'Table'[Closed Date],
            1,
            BLANK ()
        )
    )
)

Or

BugsCount =
COUNTX (
    'Table',
    IF (
        SELECTEDVALUE ( 'DateTable'[Date] ) >= 'Table'[Created Date]
            && SELECTEDVALUE ( 'DateTable'[Date] ) <= 'Table'[Closed Date],
        1,
        BLANK ()
    )
)

 

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kentyler]

I added another table to your dataset. Project

This lets you filter both milestones and bugs either by project or by date.
Here is a simple report that has a slicer based on the project field of the project table

when you click on a project in the slicer it filters both tables
You could also make a slicer based on the date of the calendar table, which would also filter both the bug report and the milestones...
In order to get counts you can just change the bug name field to count 

[Anonymous]

This definitely helps in my relationships, but I need it to display EVERY date. So I need a table like:

Date	Bug Count
1-1-2000	2
1-2-2000	2
1-3-2000	2
1-4-2000	2
1-5-2000	3
1-6-2000	3
1-7-2000	3

etc...