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

FawadRehman's avatar
FawadRehman
Helper I
10 years ago

LarsSchreiber I need your help. I am looking for same solution but somehow its not working for me. 

RunningTotal =
CALCULATE (
SUM ( TabelForecast[Forcast] );
FILTER ( ALL ( TabelForecast ); TabelForecast[MonthYear] <= MAX ( TabelForecast[MonthYear] ) );
VALUES ( TabelForecast[M#] )
)

 

 

I have tried your formula in power BI but its not giving me running total but total for all the rows :( Any suggestions?

 

 

LarsSchreiber's avatar
LarsSchreiber
Responsive Resident
10 years ago

Hi FawadRehman,

 

sorry, I haven't been here for a while. Could you solve your problem already?!

 

regards,

Lars