Forum Discussion

nhanser's avatar
nhanser
Regular Visitor
10 years ago
Solved

DAX Running total by another column

Hello,   I need help to create a DAX column/measure that will create a running total by another column.   So for example, looking at this table Item | Date | QTY ------------------...
Need Help
  • LarsSchreiber's avatar
    LarsSchreiber
    10 years ago

    Hello nhanser,

     

    for me it looks like you want to add a calculated column, but the DAX code you use is for beeing used in a pivot table. 

     

    I copied your raw data, but used different dates.

     

    raw data

     

    The pivot I created looks like this and I hope this is what you want:

     

    Pivot

     

    The code I used is close to the one you used, but slightly different:

     

    RunningTotal :=
    CALCULATE (
     SUM ( Tabelle1[QTY] );
     FILTER ( ALL ( Tabelle1 ); Tabelle1[Date] <= MAX ( Tabelle1[Date] ) );
     VALUES ( Tabelle1[Item] )
    )

     

    The key here is the VALUES() which puts the Items into the filter context of the CALCULATE()-statement. Otherwise you would have running totals on the dates, but it would be the same for all your Items. 

     

    Hope that helps a bit.

     

    Greets,

    Lars

PowerBIGuy's avatar
PowerBIGuy
Icon for Responsive Resident rankResponsive Resident
10 years ago

Have you tried:

 

On Hand = CALCULATE( SUM('Table'[QTY]),
	FILTER(
		ALLSelected('Table'),
		'Table'[Date] <= MAX('Table'[Date])
	)
)