PBI Calculate SUMX with filter using 2 tables DAX

Question

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]))

Table1 (Forecast):

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

Table 2 (M3 FGLEDG)

Alexis Olson Alexis Olson · Accepted Answer · 2020-04-04T21:56:22

This doesn't seem like a good use case for SUMX but that's not your problem. I think your first filter is always evaluating to be true as it assumes [projektNr] comes from M3 FGLEDG (DAX is not case-sensitive).

I would write the measure like this (assuming the tables don't have a relationship):

Test =
CALCULATE (
    SUM ( 'M3 FGLEDG'[Fakturerat] );
    'M3 FGLEDG'[Projektnr] = Forecast[projektNr];
    'M3 FGLEDG'[LastDayOfMonth] <= Forecast[prognosDatum]
)

PBI Calculate SUMX with filter using 2 tables DAX

1 Answers