Events in Progress

[Preetish_1]

Hello everyone, I am trying to solve a 'Events in Progress' problem.

I am trying to derive the count of all tickets that are open in a certain month.
The conditions are as follows.
- For a particular month, the ticket creation date can be the same month or a month in the past.
- If the Ticket Closing date is blank or if the Ticket closing date is after the end of the month under consideration, then the ticket is considered open in that particular month. (Tickets with closing date in the same month are not considered)
I created a Datekey table with a relationship to the Creation date in my 'Tickets' table.

I refered to  Gerhard's blog and tried to adapt the solution
https://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/

This is the formula I am trying to us, but I am not sure I am getting the right solution. I believe that I am getting the right values as far as the closing date is concerned, but the date filter is only considering 'Creation Date' values from the month under consideration.  And I am not sure if the issue with regards to the formula itself or the relationship between the tables.

Open Tickets =
 CALCULATE(
  COUNTROWS(Tickets);
   FILTER( 'Tickets'; 'Tickets'[CreationDate] <= MAX(Datekey[Date]));
   FILTER( 'Tickets'; 'Tickets'[ClosingDate] = BLANK() || 'Tickets'[ClosingDate] > MAX( Datekey[Date]))
      
   ) 

I would be glad if someone can help me solve this issue.

Thanks

[magsod]

If you remove the relationship between your Tickets table and the DateKey table you will get the right result.

(If you need the relationship for some other calculation in your model you should be able to add the crossfilter function and specifiy direction = none inside your Calculate function.) 

Br,

Magnus

[Preetish_1]

Thank you very much for your reply Magnus.

I deleted the relationship, and I now have accurate results.

Thanks again,

Preetish