Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Running Total

I'm using PBI to report on the amount of tickets in our service desk.

 

I'd like a method to track the amount of open tickets over time.
For example, today we have 100 open tickets

Yesterday we had 120

Before the Christmas break we had 150.


I'd like to be able to record that running total somewhere, so I can show the amount of open tickets over time.  Is that possible?

 

Currently, I'm tracking it on a spreadsheet, but would like an automated method.

Need Help
Show and Tell
Tips and Tricks

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Nathaniel_C 
    Sorry to chase, did you see my previous message about this?
    Cheers

    Rob

    • Nathaniel_C's avatar
      Nathaniel_C
      Community Champion
      3 years ago

      Hi Rob, Anonymous 

      At work, but will get back to you today.


      Nathaniel

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    3 years ago

    Hi Anonymous ,
    Yes, it is possible.  You did not provide much info, but below is an example:

    Running Total Measure = 
VAR varName = MAX('Table'[Name])
VAR varFruit = MAX('Table'[Fruit])
VAR varCurrentDate = MAX('Table'[Date])
VAR Result = 
    SUMX(
        FILTER(
            ALL('Table'),
            'Table'[Name] = varName
                && 'Table'[Fruit] = varFruit
                && 'Table'[Date] <= varCurrentDate
        ),
        'Table'[Quantity]
    )
RETURN
    Result

    which will give you this:


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks Nathaniel_C where do I set this up in PBI?

       

      The one field I want to count is Ticket_NO, for each day

      So, i'd want the table to look like this

       

      DateTickets
      29/12100
      30/12110
      31/12110
      01/01120

       

       

      What would the script for that look like? 

      I'm using a database called UK_DW and a view called V_VM_AMS_TICKET_REPORT_PROD_UK

       

      Thanks for your help.
      Rob

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        3 years ago

        Hi Anonymous ,
        You would write it as a measure and drop it on your table as a new column. So can you set up a table with the date and the number of tickets for that day?  If so, I can show you how to write the measure for the running total.

        Let me know if you have any questions.

        If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
        Nathaniel