Forum Discussion

SomeGuyNeedHelp's avatar
SomeGuyNeedHelp
Regular Visitor
4 years ago

How to plot cumulative status over time

Hello,

 

I'm new to the platform and I've got a simple table where each row represents a ticket, they all have a status of either "open" or "closed". I also have a column showing the date each ticket was opened and a column showing the date each ticket was closed.


I then added a custom column to show the year-week number (2022-5) for both "open" and "closed". When I plot this, it shows a count of issues that were opened on a given date, rather than the total open on that date.

Rather than seeing how many tickets were opened on a date, I want to see how many we had in total, including those that were opened weeks / years prior. Essentially, I need a plot for all open and closed tickets (cumulatively) over time, how is this best achieved?

 

Thanks

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi SomeGuyNeedHelp ,

     

    I have built a data sample:

     

    Based on your description, you may add a new Dates table :

    Dates = ADDCOLUMNS( CALENDAR(MIN('Table'[opened date]),TODAY()) ,"Year-Week",YEAR([Date])*100+WEEKNUM([Date],2))

    Then create a measure:

    Measure = 
var _minDate=CALCULATE( MIN('Dates'[Date]),ALLEXCEPT(Dates,Dates[Year-Week]))
var _maxDate=CALCULATE( MAX('Dates'[Date]),ALLEXCEPT(Dates,Dates[Year-Week]))
return CALCULATE(COUNTROWS('Table'),FILTER('Table',([closed date]=BLANK() &&[opened date]>=_minDate) ||([closed date]<=_maxDate &&[opened date]>=_minDate)))

    Output:

     

    Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

    Refer to:

    How to provide sample data in the Power BI Forum - Microsoft Power BI Community

    How to Get Your Question Answered Quickly - Microsoft Power BI Community

     

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • SomeGuyNeedHelp's avatar
      SomeGuyNeedHelp
      Regular Visitor
      4 years ago

      Hello Anonymous,

       

      Thank you for responding to this, unfortunately I think my question has been misunderstood.

      In your example data, it shows 5 issues with no closed date. This means that the most recent date (2022-11) should have a value of at least 5, not 1.

      To clarify, I need the plot to show the total number of issues open on a given date (2022-11 = 7). 

      5 Open issues (1, 2, 5, 7 & 10) + 2 closed issues that were open on 2022-11 (8 & 9) but have since been closed.

      Can you help with this?

      Thanks