Running total column against 5 columns data using DAX

• Question

• Hi everyone,

I am able to get the running total calculated column against 1 and 2 columns of same table into tabular data model but while I try to calculate the running total against 4 and 5 columns its taking very long time and stuck.

e.g.: date, col1, col2, col3, col4, col5, Amount, Running total (column names)

data into table: 500000 records

DAX using : Calculate(SUM([Amount]), Filter('Table', 'Table'[Date] <= Earlier('Table'[Date]) &&  'Table'[col1] <= Earlier('Table'[col1]) && 'Table'[col2] <= Earlier('Table'[col2]) &&  'Table'[col3] <= Earlier('Table'[col3]) && 'Table'[col4] <= Earlier('Table'[col4]) && 'Table'[col5] <= Earlier('Table'[col5])))

note: the following below measure gives me the cumulative amount.

Measure1: Calculate(SUM([Amount]), Filter(All(Calendar), Calendar[Date] <= Max(Calendar[Date]))

its gives me the result but... I need to calculate returns which depends on the above measure... so while I am using above measure for returns ..its give me very slow performance....

Monday, March 23, 2015 9:52 AM

```CALCULATE(
SUM( Table[Amount] )
,ALL( Table )
,Table[Date] <= EARLIER( Table[Date] )
,Table[col1] <= EARLIER( Table[col1] )
,Table[Col2] <= EARLIER( Table[col2] )
,Table[col3] <= EARLIER( Table[col3] )
,Table[col4] <= EARLIER( Table[col4] )
,Table[col5] <= EARLIER( Table[col5] )
)```

CALCULATE() automatically evaluates all filter arguments in a logical and.

Tuesday, March 24, 2015 9:56 PM

All replies

```CALCULATE(
SUM( Table[Amount] )
,ALL( Table )
,Table[Date] <= EARLIER( Table[Date] )
,Table[col1] <= EARLIER( Table[col1] )
,Table[Col2] <= EARLIER( Table[col2] )
,Table[col3] <= EARLIER( Table[col3] )
,Table[col4] <= EARLIER( Table[col4] )
,Table[col5] <= EARLIER( Table[col5] )
)```

CALCULATE() automatically evaluates all filter arguments in a logical and.

Tuesday, March 24, 2015 9:56 PM

I tried below formula...and its working fine for me.....

Calculate(
SUM(Table[Amount])
,ALLExcept( Table, Table[Col1], Table[Col2].... )
,Table[Date] <= EARLIER( Table[Date] )

but when I try to process the same table... its taking very long time...

No of rows: 50,00,000

can I increase the performance...

Thursday, March 26, 2015 11:56 AM
• Does anyone have a suggestion for Manikant to increase perf?

Thanks!

Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)