locked
Question regarding Earlier DAX function for obtaining Running Totals RRS feed

  • Question

  • Hi,

       I am completely new to DAX. I am working on AdventureWorks DW database and I have already created a basic tabular model and deployed into a server. I am trying to write a DAX statement where I can get the running totals for the Sales Amount order by Order Date column.

    EVALUATE
    SUMMARIZE (
        'Internet Sales',
        'Internet Sales'[Order Date],
        "Sales", SUM ( 'Internet Sales'[Sales Amount] ),
        "Running Total", SUMX (
            FILTER (
                'Internet Sales',
                'Internet Sales'[Order Date]
                    < EARLIER ( 'Internet Sales'[Order Date], 1 )
            ),
            'Internet Sales'[Sales Amount]
        )
    )

    But I am not getting the expected result all I am getting is the blank value for the Running total column. 

    I just want to know whether I am missing anything in the query

    Regards


    Venkata Koppula

    Wednesday, June 3, 2015 8:57 AM

Answers

  • Hi Venkata,

    According to your description, you want to calculate the Running Total in DAX. Right?

    In this scenario, since you are using EARIER() function to compare the current row against all other rows, your sum calculation should be inside of the loop. So you query should be like:

    EVALUATE
     SUMMARIZE (
         'Internet Sales',
         'Internet Sales'[Order Date],
         "Sales", SUM ( 'Internet Sales'[Sales Amount] ),
         "Running Total", CALCULATE (
        SUM ( 'Internet Sales'[Sales Amount] ),
        FILTER (
            ALL ( 'Internet Sales'[Order Date] ),
            'Internet Sales'[Order Date] <= EARLIER( 'Internet Sales'[Order Date] )
        )
    ) )

    Reference:
    Running Total Techniques in DAX

    DAX: Running Totals
    Cumulative Total

    Regards,


    Simon Hou
    TechNet Community Support


    Thursday, June 4, 2015 9:45 AM