Running total per period

[Shockedrope]

I am trying to calculate an open ticket running total. My data looks like this:

Ticket Name	Open Date	Closed Date
AB	1-1-2015
CD	2-2-2015	5-2-2015
EF	4-6-2016
GH	4-8-2018	8-9-2018
IJ	7-10-2019	7-12-2019
KL	10-30-2019	11-30-2019
MN	12-20-2019	1-15-2020
OP	1-17-2020

If a ticket does not have a close date it is considered still open. I am looking for a running total per month or week so that I can create a graph of open tickets over time per period.

[Shockedrope]

Thank you for the suggestions. I was able to find my solution by following this article: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Ashish_Mathur]

Hi,

If you want a running total by month, then i can help.  Also, do you want a running total since inception or should the counter reset at the start of each year?

[amitchandak · Accepted solution]

My blog on HR deals with the same problem. Current employees like open tickets

HR-Analytics-Active-Employee-Hire-and-Termination-trend

It needs handling of open dates along with the close date and date dimension.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[kentyler]

If you use a month as a period. Does that define an open ticket as one what was opened before the month, or during the month, but still had not closed by the end of the month ? What if you use a week as a period ? Is the logic the same, but with weeks?
So your count of "open" may vary depending on the time period.

What you have is what they call a "slowly changing dimension"... your service record changes over time and when you want to report on it all you have available is the current version.

One possibility is to take snapshots. Decide on a reporting period. Record a data table of tickets that are open at the end of each period and how many days they have been open.

Then you could write reports against that table.

Power BI is easy to learn, but it has hard parts. I'm a personal Power Bi Trainer I learn something every time I answer a question

[VasTg]

@Shockedrope 

Create a new calculated column as below.

Open? = IF(ISBLANK(Table[Closed Date]),1,0)

The join the table with date dimension(below) based on Open date.

Calendar = CALENDAR(MIN(Table[Open Date]),MAX(Table[Closed Date]))

Create a visual with month from date dimension and measure from the table

Cumulative sum = CALCULATE(SUM(Table[Open?]),FILTER(ALL(Table[Open Date]),Table[Open Date]<=MAX(Table[Open Date])))

If it helps, mark it as a solution

Kudos are nice too

[danextian]

Hello @Shockedrope ,

Try this:

Create a calculated calendar table like below:

Dates = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2015, 1, 1 ), TODAY () ),
    "YYYYMM", FORMAT ( [Date], "YYYYMM" ),
    "Year", YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "MMMM" ),
    "Month Number", MONTH ( [Date] )
)

Create a relationship single direction one-to-many relationship between Open Date from your fact and the Date from the calculated table.

Create this measure 

Open Tickets =
CALCULATE (
    //count of tickets
    COUNTA ( 'Table'[Ticket Name] ),
    //filter tickets opened on or before the max date in the current context
    FILTER (
        ALL ( Dates ),
        Dates[Date] <= MAX ( Dates[Date] )
    ),
    //from the filter above, filter tickets without or with future closed dates
    FILTER (
        ALL ( 'Table'[Closed Date] ),
        'Table'[Closed Date]
            > MAX ( Dates[Date] || ISBLANK ( 'Table'[Closed Date] ) )
    )
)

Add YRMO column from the Dates table and Open Tickets Measure to a table visual to test the formula.

Download the sample pbix from here: https://drive.google.com/file/d/1tqLK-aPakHWxxAKr0UwxA2bQjll-gO1T/view?usp=sharing