cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nhanser Frequent Visitor
Frequent Visitor

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
----------------------------------
    a    | 12/31/2015 |    1
    a    | 12/31/2015 |    -1
    a    | 12/31/2015 |    2
    b    | 12/31/2015 |    10
    b    | 12/31/2015 |    -5
    b    | 12/31/2015 |    1

What I need at the end is to add another field/column that will create a running total, something like this:

 Item  |       Date      |  QTY  | RunTotal
------------------------------------------------
    a    | 12/31/2014  |    1     |     1
    a    | 1/15/2015    |    -1   |      0
    a    |  2/28/2015   |    2     |     2
    b    | 12/31/2015  |    10   |   10
    b    | 4/6/2015      |    -5   |     5
    b    | 5/5/2015      |    1     |    6

I have tried using a calucalte function and filetering on the date, but it only seems to create a running total for everytihng, not filtering by the item.  Similar to this:

 

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

 

Any ideas would be much appericated.  Thanks for any and all help in advance!

1 ACCEPTED SOLUTION

Accepted Solutions

Re: DAX Running total by another column

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

15 REPLIES 15
PowerBIGuy Member
Member

Re: DAX Running total by another column

Have you tried:

 

On Hand = CALCULATE( SUM('Table'[QTY]),
	FILTER(
		ALLSelected('Table'),
		'Table'[Date] <= MAX('Table'[Date])
	)
) 
Business Intelligence Architect / Consultant

Re: DAX Running total by another column

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

jmalone Member
Member

Re: DAX Running total by another column

@LarsSchreiber - I was looking for a similar solution as @nhanser. Your solution worked perfectly!

 

The key, as you mentioned, is the VALUES() function to put the running total calculation in context of the field needing to be grouped.

 

Thank you for the very useful response!

Re: DAX Running total by another column

@jmalone,

 

I am happy, when it helped Smiley Happy

 

Regards,

Lars

PeterBI Regular Visitor
Regular Visitor

Re: DAX Running total by another column

 
robbie337 Frequent Visitor
Frequent Visitor

Re: DAX Running total by another column

This is a great solution, thank you. Worth noting you can stack multiple variables in the value cluase to allow aggregation across multiple / different dimensions, (similar to partition by in sql I guess).

 

However, I have run into a slight issue. If you have a value or combination of values that are empty i.e. there is no row in your dataset for that value or combination of values, the running total becomes incorrect until the next time a row matching those criteria exists, at which point it 'catches up'.

 

For example, if you are creating a running total of sales by month and team across a year, using a data set like:

 

Month Team Sales

Jan      A       100

Jan      B       200

Feb     A       150

Feb     B       210

Mar     A       300

Apr     A       120

Apr     B       400

 

In March, if Team B has a month where they have no sales (and therefore no row in the dataset), your running total for the business as a whole will ignore all Team B's previous data in the calculation of a running total, returning 550 as the total, rather than 690.

 

I'm yet to find a fix for this (despite habving tried every combination of isempty i can think of). If anyone has any ideas I'd love to hear them


Robbie

 

The following month if Team B has

FawadRehman Regular Visitor
Regular Visitor

Re: DAX Running total by another column

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

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 Smiley Sad Any suggestions?

 

 

Re: DAX Running total by another column

Hi @FawadRehman,

 

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

 

regards,

Lars

 

 

FawadRehman Regular Visitor
Regular Visitor

Re: DAX Running total by another column

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 150 members 2,042 guests
Please welcome our newest community members: