none
Running Totals using DAX giving unexpected results

    Question

  • I have read all of the various articles across the Internet dealing with running totals, and am still befuddled.  Writing SQL is easier than this torture with DAX.

    i tried this formula to get a running total by item:

    =CALCULATE(SUM([Inventory Line Combo Quantity]),FILTER(ALL('INVENTORY DETAILS'),SUMX(FILTER('INVENTORY DETAILS',EARLIER('INVENTORY DETAILS'[Inventory Line Forecast Date])<='INVENTORY DETAILS'[Inventory Line Forecast Date]),'INVENTORY DETAILS'[Inventory Line Combo Quantity])))

    Noticed that I have a Date in the fact table itself (too long a story for that one).  So this should be pretty straight forward.

    Here is the result:

    Monday, June 24, 2013 9:32 AM

Answers

  • how about this one:

    CALCULATE(
         SUM([Inventory Line Combo Quantity]),
         FILTER( 
    		CALCULATETABLE(
    			'INVENTORY DETAILS',
    			ALL('INVENTORY DETAILS'[Inventory Line Forecast Date]),
    			ALL('INVENTORY DETAILS'[Inventory Document Type])),
         'INVENTORY DETAILS'[Inventory Line Forecast Date] 
             <= MAX ( 'INVENTORY DETAILS'[Inventory Line Forecast Date] )
     )


    - www.pmOne.com -

    Monday, June 24, 2013 3:44 PM

All replies

  • If this is a measure, why you don't use:

    CALCULATE(
        SUM([Inventory Line Combo Quantity]),
        FILTER( ALL('INVENTORY DETAILS'[Inventory Line Forecast Date]),
        'INVENTORY DETAILS'[Inventory Line Forecast Date]
            <= MAX ( 'INVENTORY DETAILS'[Inventory Line Forecast Date] )
    )


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    Monday, June 24, 2013 2:30 PM
  • I had originally tried a simpler version, such as you suggest, but it only works if I remove the inventory document type field. 

    I can do just a show values as running total to get the proper result - but again, once I put in the inventory document type field, I cannot get a running total that ignores that context.

    Here is the result of plugging your formula:

    If I remove the doc type field, then i get the desired results as shown below.  What I am trying to figure out is how do I create this formula that avoids that context?

    Lastly, if I switch positions of the doc type and date, then of course the dates would not be in correct order.

    Monday, June 24, 2013 3:24 PM
  • how about this one:

    CALCULATE(
         SUM([Inventory Line Combo Quantity]),
         FILTER( 
    		CALCULATETABLE(
    			'INVENTORY DETAILS',
    			ALL('INVENTORY DETAILS'[Inventory Line Forecast Date]),
    			ALL('INVENTORY DETAILS'[Inventory Document Type])),
         'INVENTORY DETAILS'[Inventory Line Forecast Date] 
             <= MAX ( 'INVENTORY DETAILS'[Inventory Line Forecast Date] )
     )


    - www.pmOne.com -

    Monday, June 24, 2013 3:44 PM
  • Actually that worked.  You couldn't see it, but the field was inventory document forecast type.

    So it appears now, however, that every time I want to ensure that this formula is correct, I have to include (in the calculate table options) what ever fields are placed after the inventory line forecast date.

    Is there a way to avoid that?

    Here is a link to the powerpivot if that helps.

    https://dl.dropboxusercontent.com/u/9233846/test_inventory_f.xlsx

    • Edited by CLEARIFY Monday, June 24, 2013 5:37 PM Add information
    Monday, June 24, 2013 5:26 PM
  • Now I understand your need, but you have the following problem: DAX is completely unaware of the user interface (i.e. the PivotTable) in which you are placing the measures you defined. From this point of view, MDX provides some more info (through the AXIS object) but this doesn't mean that it's easier than MDX (actually, the opposite is true).

    Thus, there is no direct way to detect in your formula what is the order in which the user put attributes in the PivotTable. You can use functions such as ISFILTERED and ISCROSSFILTERED, but in reality you are not able to discriminate the order of them - so you would not know whether Item Name and Inventory Document are before or after the Inventory Line Forecast Date in your example.

    You might try to forecast the possible meaningful combinations and with some IF statement trying to use the proper formula, but I'm not sure about the usability of such approach. Probably a better approach is working on the client side - an Excel AddIn has more information about the user interaction and could build the measure on the fly, but the problem that at this time creating a measure by code is not supported by Microsoft (you can find more info here).


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    Tuesday, June 25, 2013 6:40 AM
  • Since the running total serves a particular function for people wanting to see what their inventory levels would be in the future, a finite set of columns is appropriate, and not problematic.  We used this today for a few people, under the guise that 'tell us what you want' and we will create the appropriate formula.  Works like a champ, even with the limitations.

    What I was totally surprised by Gerhard's solution was that even if I moved the forecast date to the right of other row labels, the running total STAYED with the line item.  I didn't expect that; I had surmised as you pointed out above, that the order of the columns would dictate (e.g. dates have to be in order for a correct running total).  But, not the case as I found out:  Compare this to my earlier post.

    I have to learn more about the CALCULATETABLE functionality, because it seems to act as a SQL Subquery of sorts.

    You guys are my heroes here.

    Tuesday, June 25, 2013 7:04 AM