Forum Discussion

Jelto's avatar
Jelto
Regular Visitor
6 years ago
Solved

Calculate other table with active filter

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 numberPosting dateTot. werk. omzet
281024-4-2020my calculation
281124-4-2020my calculation
281224-4-2020my calculation

Table B (waardepost)

Entry numberEntryNo.TableAPosting dateTot. werk. omzet
1281024-4-2020300,25
2281020-4-2021100
3281124-4-2020200

 

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?

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.