cancel
Showing results for
Did you mean:
Helper I

## Calculate SUMX with filter using 2 tables DAX

Hi,

I want to add the running total for each row in table 1 from data in table 2. I have been trying to use the Calculate formula together with the Sumx and filter but for some reason my filter doesn't seem to be working properly. If there is way to do this in M I would greatly appreciate any pointers to that as well.

Right now I'm using the SUMX as that evaluates every row (explained here: https://exceleratorbi.com.au/use-sum-vs-sumx/)

This is my current formula in order to calculate the test Column:

```Test = CALCULATE(SUMX('M3 FGLEDG';'M3 FGLEDG'[Fakturerat]);
FILTER('M3 FGLEDG';'M3 FGLEDG'[Projektnr] = [projektNr]);
FILTER('M3 FGLEDG';'M3 FGLEDG'[LastDayOfMonth] <= [prognosDatum]))```

ALso tried:

```Test = CALCULATE(SUMX('M3 FGLEDG';'M3 FGLEDG'[Fakturerat]);
FILTER('M3 FGLEDG';'M3 FGLEDG'[Projektnr] = [projektNr] && 'M3 FGLEDG'[LastDayOfMonth] <= [prognosDatum])```

Table1 (Forecast):

The first row with the date 2016-04-30 should be: 23 389 598 in column test.

Table 2 (M3 FGLEDG)

1 ACCEPTED SOLUTION
Community Support

``````Test =
VAR a = 'Forecast'[Date]
VAR n = Forecast[ProjectNum]
RETURN
CALCULATE (
SUMX ( 'M3 FGLEDG', 'M3 FGLEDG'[Amount] ),
FILTER ( 'M3 FGLEDG', 'M3 FGLEDG'[ProjectNum] = n ),
FILTER ( 'M3 FGLEDG', 'M3 FGLEDG'[Date] <= a )
)
``````

Or we can use that to work on it.

``````Column = var d = 'Forecast'[Date]
var n = 'Forecast'[ProjectNum]
return
CALCULATE(SUM('M3 FGLEDG'[Amount]),FILTER('M3 FGLEDG','M3 FGLEDG'[Date] <=d && 'M3 FGLEDG'[ProjectNum] = n))``````

Pbix as attached.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
3 REPLIES 3
Community Support

``````Test =
VAR a = 'Forecast'[Date]
VAR n = Forecast[ProjectNum]
RETURN
CALCULATE (
SUMX ( 'M3 FGLEDG', 'M3 FGLEDG'[Amount] ),
FILTER ( 'M3 FGLEDG', 'M3 FGLEDG'[ProjectNum] = n ),
FILTER ( 'M3 FGLEDG', 'M3 FGLEDG'[Date] <= a )
)
``````

Or we can use that to work on it.

``````Column = var d = 'Forecast'[Date]
var n = 'Forecast'[ProjectNum]
return
CALCULATE(SUM('M3 FGLEDG'[Amount]),FILTER('M3 FGLEDG','M3 FGLEDG'[Date] <=d && 'M3 FGLEDG'[ProjectNum] = n))``````

Pbix as attached.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Super User

Hey @patrickbender ,

please create a pbix that contains sample data but still reflects your data model, upload the pbix to onedrive or dropbox and share the link.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Helper I

Hi, I made some changes so it better matches what I have in my dataset. Just a small change on how to identify cost and invoices.

I want to use the transactions table to get the columns invoiced and projected cost in the forecast table. I created them there with the corect amounts in order to be able to verify what my intentions are.

Thank you for looking at this @TomMartens

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors