Need help with a In Progress ticket status count

[millerz1]

I was referencing the below thread and trying to use a previous solution.  

Solved: Re: Help With Event In Progress Visualization - Microsoft Power BI Community

I have a sharepoint online list that we use to track hypercare items (think of it like a help desk ticket system).  I am trying to create a table that displays the number of new items created, closed, and in progress by a daily visual.  I am unable to find a solution that works for the in progress quite right.

Each ticket is represented as one row in a list, which has a created date and closed date.  I tried to create a new table as seen below with the following logic.

Any suggestions or thoughts would be greatly appreciated.

Defects Open = 
    VAR EventsCreated =
        CALCULATETABLE(
            VALUES('R3 0 Hypercare List'[Id]),
            FILTER(
                ALL('R3 0 Hypercare List'),
                'R3 0 Hypercare List'[Created] <= MAX('Dates'[Date])
            )
        )
    VAR EventsPreviouslyClosed =
        CALCULATETABLE(
            VALUES('R3 0 Hypercare List'[Id]),
            FILTER(
                ALL('R3 0 Hypercare List'),
                'R3 0 Hypercare List'[Date Closed] < MIN('Dates'[Date])
            )
        )
    RETURN

    COUNTROWS(
        EXCEPT(
            EventsCreated,
            EventsPreviouslyClosed
        )
    )

Edit: The second image wasn't very good quality so I updated.  Thanks again.

[TomMartens]

Hey @millerz1 ,

thanks for providing the sample file, unfortunately the pbix shows only flawed visuals on the report pages the reports. Not sure if this is relevant to understand what you are going to achieve or if this is due to creating the sample data.

Regards,

Tom

Please comment, if these errors can be ignored

[TomMartens · Accepted solution]

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

[millerz1]

@TomMartens  - Now how would you add columns of issues opened on each date and the number of issues closed each date?

Here is what I am trying to do.

Thanks so much for your help and time.

[TomMartens]

Hey @millerz1 ,

please create a pbix file that contains sample data, but still reflects your data model, upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data share the xlsx as well.

Please, describe the expected result based on your sample data.

Regards,

Tom

[millerz1]

Will do on Monday.  I'm not sure of an easy way to get the sample data into a format that I can share due to security reasons.

I appreciate the response.

[millerz1]

@TomMartens 

Link to power bi file.
https://crownequipmentcorporation-my.sharepoint.com/:u:/g/personal/zack_miller_crown_com/EWnNcmz5NJh...

Link to data export from SharePoint Online List to Excel

https://crownequipmentcorporation-my.sharepoint.com/:x:/g/personal/zack_miller_crown_com/EVNvsecF3Wt...