Calculate other table with active filter

[Jelto]

Hi,

After using Google for two hours without the expection result I Really need your help. I have the following case:

Table A (artikelpost)

Entry number	Posting date	Tot. werk. omzet
2810	24-4-2020	my calculation
2811	24-4-2020	my calculation
2812	24-4-2020	my calculation

Table B (waardepost)

Entry number	EntryNo.TableA	Posting date	Tot. werk. omzet
1	2810	24-4-2020	300,25
2	2810	20-4-2021	100
3	2811	24-4-2020	200

In my table A I want to sum column Tot. Werk. Omzet from table B. I can do this easily with the following dax formula:

Tot. Werk. Omzet = CALCULATE(SUMX(WS_Waardeposten;WS_Waardeposten[Tot. Werk. Omzet]);FILTER(WS_Waardeposten;WS_Artikelposten[Volgnummer] = WS_Waardeposten[Artikelpostnr.]))

Except when I filter my table B on posting date with a slicer (example: 1-1-2020 untill 25-4-2020) I only want to sum entry number 1 in table B and not entry number 1+2.

How can I do this and if possible could you explain what I am doing wrong?

[v-juanli-msft · Accepted solution]

Hi @Jelto 

Create a measure

Measure =
CALCULATE (
    SUM ( TableB[Tot. werk. omzet] ),
    FILTER (
        TableB,
        TableB[EntryNo.TableA]
            = MAX ( TableA[Entry number] )
            && TableB[Posting date]
                <= MAX ( 'date'[Date] )
            && TableB[Posting date]
                >= MIN ( 'date'[Date] )
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.