Running Total

[Anonymous]

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.

[Anonymous]

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

Rob

[Nathaniel_C]

Hi Rob, @Anonymous 

At work, but will get back to you today.

Nathaniel

[Nathaniel_C]

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]

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

Date	Tickets
29/12	100
30/12	110
31/12	110
01/01	120

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]

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