Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter based on date range in another table

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

Bug TitleCreated DateClosed DateProject
Bug 11-1-20001-30-2000Project 1
Bug 21-5-20001-25-2000Project 1
Bug 31-1-20002-10-2000Project 1
Bug 41-2-20001-30-2000Project 2
Bug 51-1-20001-30-2000Project 2

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

MilestoneProjectMilestone Date
Milestone 1Project 112-31-1999
Milestone 2Project 11-15-2000
Milestone 3Project 11-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?

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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.

3 Replies

  • kentyler's avatar
    kentyler
    Solution Sage
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

      DateBug Count
      1-1-20002
      1-2-20002
      1-3-20002
      1-4-20002
      1-5-20003
      1-6-20003
      1-7-20003

      etc...

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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.