How to count values at a certain date

[Dasbas]

Hello,

I am trying to create a dashboard for a service call system. In this system I have a Call date (open date) and a Close date. What i want to do is create a line graph which shows at what time how many calls were open.

I thought I could solve this by creating a Column which counts the rows where the Close date is smaller than the open date. This however doesn't work since it will just count all the rows since for one row the close date is never before the open date.

Is there a way to store the open date for one row and then count how many rows have a close date before said open date?

Thank you!

[LaurentCouartou]

I understand you want to calculate the number of calls that were open before a given date and have been closed on or after this date.

Adding a Dates table to your model, and then filtering on it will make things easier. Following best practise, your Dates table should contain contiguous dates.

The measure would look like:

[Number of calls opened at] = CALCULATE( COUNTROWS( Calls )
      , FILTER( VALUES( Calls[opendate] )
                ,  [opendate]  < MAX( Dates[date] )
      )
      , FILTER( VALUES( Calls[closedate] )
                ,  [opendate]  >= MAX( Dates[date] )
      )
)

MAX( Dates[date] ) implies the results shown, if you select more than one date, will be for the last date in the selected period.

[Dasbas]

Hi Laurent,

Thank you for you answer.

Tyring to implement your advice as we speak, however this gives me an issue: 

[opendate]  < MAX( Dates[date] )

[opendate] refers to a column and not a single value. Do you have any idea how to fix this? 

I also can't create a releationship between the dates table and the call date due to the lacking of unique values?

[LaurentCouartou · Accepted solution]

I forgot to mention a date should only appear once in your Dates table.

Regarding the filter expression, I made a copy and paste error.

[Number of calls opened at] = CALCULATE( COUNTROWS( Calls )
      , FILTER( VALUES( Calls[opendate] )
                ,  [opendate]  < MAX( Dates[date] )
      )
      , FILTER( VALUES( Calls[closedate] )
                ,  [closedate]  >= MAX( Dates[date] )
      )
)

[Dasbas]

Thank you!