locked
Running total for hours reseting on change of field RRS feed

  • Question

  • Apologies for the "newbie" question, but just transitioning into DAX and Power Pivot with a Crystal Reports background.

    I have a table called PJLabDet.Total_Hrs which is the sum of hours of 7 days.

    I would like to get a running total for this field which would reset when my group (called PJLabDat.pjt_entity) changes.

    From doing a quick search there are some functions that can do this, but I get confused.  My "teach yourself" DAX book is on back order:-)

    TIA

    Peter

    Friday, October 23, 2015 10:05 PM

Answers

  • Hi Peter,

    To achieve this requirement, please refer to the sample DAX expression below.
    =CALCULATE(
        SUM( test1026[Hours] ),
        FILTER(
            ALLEXCEPT(
                test1026,
                test1026[Project]
            ) ,
        test1026[Date] <= EARLIER( test1026[Date] )
        )
    )

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

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Wednesday, November 4, 2015 7:56 AM
    • Marked as answer by Charlie Liao Monday, November 9, 2015 8:03 AM
    Monday, October 26, 2015 6:36 AM

All replies

  • Hi Peter,

    it can be easily done with the help of CALCULATE, SUM & FILTER functions in DAX. Suppose you have a Date Dim & ProductSales Fact where you have quantity as a measure and you want a running total montly i.e. Jan, Feb, Mar... then with below code you can do it.

    Cumulative Quantity :=
    CALCULATE (
        SUM ( ProductSales[Quantity] ),
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date] <= MAX ( 'Date'[Date] )
        )
    )

    Monday, October 26, 2015 3:23 AM
  • Hi Peter,

    To achieve this requirement, please refer to the sample DAX expression below.
    =CALCULATE(
        SUM( test1026[Hours] ),
        FILTER(
            ALLEXCEPT(
                test1026,
                test1026[Project]
            ) ,
        test1026[Date] <= EARLIER( test1026[Date] )
        )
    )

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

    Regards,


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Charlie Liao Wednesday, November 4, 2015 7:56 AM
    • Marked as answer by Charlie Liao Monday, November 9, 2015 8:03 AM
    Monday, October 26, 2015 6:36 AM