Need help with a In Progress ticket status count

Hey millerz1 ,

next to my question regarding the flawed visuals from the sample pbix.

I created something ...

But before I come to my little solution, please allow me to point you to this article: Events-In-Progress | Gerhard Brueckl on BI & Data (gbrueckl.at) This article links to all relevant articles that relate to the events-in-progress problem, and by itself presents a very interesting solution for a very interesting problem. If you are not that familiar with the events-in-progress challenge. I recommend start reading with the article by SqlJason..

My solution starts with an unrelated Date table, this table will be used on the axis of slicers, or feeding the content of slicers. I used this DAX statement to create the table:

Dates Unrelated = 
var _StartDate = MIN( 'Table_query__8'[Created On] )
var _EndDate = MAX( 'Table_query__8'[Date Closed] )
return

CALENDAR( _StartDate , _EndDate )

Of course, it's not important how this table looks like (meaning the number of columns), the important thing is that this table is not related to the table that contains the events.

Then I created this measure

open defects tom = 

var _MinDate = CALCULATE( MIN( 'Dates Unrelated'[Date] ) )
var _MaxDate = CALCULATE( MAX( 'Dates Unrelated'[Date] ) )
return

COUNTX(
    'Table_query__8'
    , var __DateStarted = 'Table_query__8'[Created On]
    var __DateClosed = 'Table_query__8'[Date Closed]
    return 
    IF( __DateStarted <= _MinDate && ( ISBLANK( __DateClosed ) || __DateClosed > _MinDate )
    , 1
    , BLANK()
    )
) 

This allows to create a visual like this:

Please be aware that I use the date column from the unrelated table on the axis. The measure is simple, as it only reflects on the open daily events, meaning the axis of the visual depicts the smallest detail - a day.  for this reason, not all the defined variables are also used. If you also want to know how many events are open during a given time frame like a month, then the conditions get more complex but are described in the above-referenced articles as well.

Hopefully, this provides some new insights on how to tackle your challenge.

Regards,

Tom