cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.