Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Running total for 'row count'

  • 1.  Running total for 'row count'

    Posted Feb 12, 2019 03:34 AM

    Hi,

    I'm looking for a variant of the often mentioned running total calculation.
    My data does not contain the numbers to add up in the running total, like most Sales data examples around.
    My data IS the number to add up, each row being a ticket at a Service Desk.

    I would like to know the running total for tickets created and/or closed, by Date:

    Source data
    Nr Creation Closure
    INC0001 1/01/2019 1/05/2019
    INC0002 1/01/2019 1/06/2019
    INC0003 1/02/2019 1/06/2019
    REQ0001 1/01/2019 1/07/2019
    REQ0002 1/02/2019 1/07/2019
    REQ0003 1/02/2019 1/08/2019


    Filtering 'INC*' from 'REQ*' can be done on a visual level, so I don't think that needs to be in the code(?)

     So I would like to end up with these results (not necessarily combined in 1 measure/visual/...):

    Incidents opened   Incidents closed  
           
    01/2019 2 05/2019 1
    02/2019 3 06/2019 3
    Requests opened   Requests closed  
           
    01/2019 1 07/2019 2
    02/2019 3 08/2019 3


    Thanks for the help!


  • 2.  RE: Running total for 'row count'

    Posted Feb 12, 2019 06:50 AM

    Hi Bart,

    Could you please check below links:

    https://www.daxpatterns.com/cumulative-total/

    https://javierguillen.wordpress.com/2012/11/28/running-total-techniques-in-dax/

    https://community.powerbi.com/t5/Desktop/DAX-Running-Total-YTD/td-p/21576

    Regards,
    Maruthi



    ------------------------------
    Maruthi Siva Prasad
    Data warehouse developer
    Unity
    Charlottenlund
    91654588
    ------------------------------



  • 3.  RE: Running total for 'row count'

    Posted Feb 12, 2019 07:14 AM
    Hi Maruthi,

    Thanks for the reply.
    These links all refer to what I called the more general 'Sales data examples'.

    All these cases have a column in the data stating how many items were sold etc.From there it is 'easy' to create a running total from that.
    I however do not have such a column. My data is not about transactions, it is a simple table of tickets and some of their properties, one row for each ticket.
    Please see the sample data I provided and the result I am trying to get.
    My first thought would be to use COUNTA, to generate an amount of filtered rows. Then to run that across the Date filter to get a running total. I'm not getting a correct result though...

    Thanks again!


    Sample data:
    Nr Creation Closure
    INC0001 1/01/2019 1/05/2019
    INC0002 1/01/2019 1/06/2019
    INC0003 1/02/2019 1/06/2019
    REQ0001 1/01/2019 1/07/2019
    REQ0002 1/02/2019 1/07/2019
    REQ0003 1/02/2019 1/08/2019


    Required results:

    Incidents opened  
       
    01/2019 2
    02/2019 3

    Incidents closed  
       
    05/2019 1
    06/2019 3

    Requests opened  
       
    01/2019 1
    02/2019 3

    Requests closed  
       
    07/2019 2
    08/2019 3



  • 4.  RE: Running total for 'row count'

    Gold Contributor
    Posted Feb 12, 2019 03:44 PM
    Hi Bart Van,

    To calculate the running total, first, you have to create a date dimension and then link that your required table. Unless you have a date dimension table it will be very difficult for you to use the time-related function in DAX.

    To create a date dimension, you have to create a calculated table. Use the below code for a simplest date dimension table.

    dimDate =
    ADDCOLUMNS (
    CALENDAR ("01/01/2010", NOW()),
    "Year", YEAR ( [Date] ),
    "QuarterOfYear", FORMAT ( [Date], "Q" ),
    "MonthNumber", FORMAT ( [Date], "MM" ),
    "ISODate", FORMAT ( [Date], "DDMMYYYY" ),
    "MonthName", FORMAT ( [Date], "mmmm" ),
    "MonthInCalendar", FORMAT ( [Date], "mmm YYYY" ),
    "QuarterInCalendar", "Q" & FORMAT ( [Date], "Q" ) & " " & FORMAT ( [Date], "YYYY" ),
    "DayInWeek", WEEKDAY ( [Date] ),
    "DayOfWeekName", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ))


    Regards,

    ------------------------------
    Tuhin Tapadar
    Data Analyst
    Dublin
    894164048
    ------------------------------