Calculate SUMX with filter using 2 tables DAX

[patrickbender]

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)

[v-frfei-msft · Accepted solution]

Hi @patrickbender ,

Please update your formula as below.

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.

[TomMartens]

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

[patrickbender]

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 

Link: https://bendersab-my.sharepoint.com/:u:/g/personal/patrick_benders_se/ET2H23DzngNNhZNUZazXR4UBQRTQjY...