Count by Date using filters

[CROforce]

Here is my sample data table called “observation” :

I have also created new Table called “Calendar” in Query editor which contains Date that goes from 9/30/2015 until today and I also have Index column.

“observation” and “Calendar” are related by “Date Opened” and “Date Open”.

What I am trying to do is to have a column that would show me how many items where open based on Date opened from “observation” and Date Open from “Calendar” while filtering whatever user selects from “Gating Milestone” located under “observation”.

In other words, if user selects “PVT 1” filter, calculations should show total values based on that date… Something like this:

If user selects multiple filters like PVT 1 and DVT 1, then total should be something like this: 

So far I have this formula for open: 

Open = 
    COUNTX(
        FILTER ( 'observation', 'observation'[Date Opened] = 'Calendar'[Date Open] ),
        'observation'[Date Opened]
    )

And this one for closed:

Closed = 
    COUNTX (
        FILTER ( 'observation', 'observation'[Date Closed] = 'Calendar'[Date Open] ),
        'observation'[Date Closed]
    )

But those always show static number and do not incorporate filtering of “Gating Milestone” … Is there a way to add filter to incorporate filtering by user selection of “Gating Milestone”?

[dkay84_PowerBI · Accepted solution]

Rather than custom formulas, have you tried the "Group By" funcitonality  within the query editor?

[CROforce]

That actually worked (the filtering part)... Thank you for the help. However, when I try to create Measure to calculate "Net Open" over time period to show on the Chart, it's not giving me right number based on filters selected:

Here is my table (gruped by) and I have also addedd Index to this to start from 1:

Then I have created Messure to count Net Open overtime using the same formula as in my previous attempt which worked (but I couldn't figureouit Gating Milestone filtering):

Now calculations here are not right as you can see numbers do not match:

And if I select any filter from the Gating Milestone, the numbers would be totally wrong 😞

This is what the chart should look like (blue line)

[dkay84_PowerBI]

Can you post some sample data or the PBIX for us to play with?

[CROforce]

I have just figured it out... I had to use ALLSELECTED in my formula instead of ALL

Net OPEN = 
    CALCULATE (
        SUM ( 'Open'[Open] ) ,
        FILTER ( ALLSELECTED ( 'Open' ), 'Open'[Index] <= MAX( ( 'Open'[Index] ) )
    ))