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 h...
Need Help
Show and Tell
Tips and Tricks
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