Calculate a new column using data from the same row

[SteveIves]

I have a table in Power BI with the following 3 columns:

Incident Number, Date Opened, Date Closed

It has about 1,000 rows, covering 3 years.

I want to create a graph showing how many incidents were open on each of the dates in the table. To that end, I have created a date table with all dates covered by the Incident table:

Dates = CALENDAR(MIN('Incidents'[Date Opened]), MAX('Incidents'[Date Opened]))

For every date, I want to create a count of how many incidents were open (so either a close date after that date or no close date (incident is still open).

So what I need is to do is something like :

Dates[Open Count] = COUNTROWS(FILTER('Incidents', ('Incidents'[Date Opened] < 'Dates[Date]) AND ('Incidents[Date Closed] > 'Dates[Date] OR  'Incidents[Date Closed] = BLANK() ))

 (I know that's not valid DAX).

So for every row in my 'Dates' table, get the number of Incidents that were open on that date (i.e. the open date is before the date we are looking at in the 'Dates' table and closed date is later than that date or blank).

This is so we can see the trend in the number of open incidents. 

Thanks, Steve

[lbendlin]

add the table name, slap a MAX() around it or use variables.  The exact syntax depends on your scenario.

[SteveIves]

Thanks for that. 

I've not managed to try it yet as I'm getting the error:

It's also showing that the [Date] field isn't found, which I worked out is due to this error:

Did that expression work for you?

[lbendlin]

Nearly there.

[Open Count] = CALCULATE(COUNTROWS('Incidents'),'Incidents'[Date Opened] < [Date]),COALESCE('Incidents[Date Closed],[Date]) > [Date])