DAX Sum Quantity by Month per Product in a different table

[Anonymous]

Hi everyone,

I have the following columns in Power BI:

1. "full updated ALL PARTS " table: Order Quantity; Month; Material Number; Vendor Name
2. "Part Numbers" table: Part Number; Vendor

I'm trying to create a measure where it can calculate Total Order Quantity by Month per Part Number.

I came up with this:

Sum amount = CALCULATE ( SUM ( 'full updated ALL PARTS (2)'[Order Quantity] ), DISTINCT ( 'Part Numbers'[Part Number]))

, however, I don't know how to filter the result by month.

Sorry this might look really obvious to some of you but I have very little knowledge with DAX so any helps and recommendations are all appreciated!

Thank you!!!

[Greg_Deckler]

So, there is no relationship between the two tables or does your first table also contain part number?

[Anonymous]

Hi sorry, I forgot to write it down (already fixed the description)  but the 2 tables do link to each other by Part Number and Material Number columns.

[v-piga-msft · Accepted solution]

Hi @Anonymous,

Based on your requirement, what about creating the measure with the formula below?

Measure =
CALCULATE (
    SUM ( 'full updated ALL PARTS'[Order quantity] ),
    ALLEXCEPT (
        'full updated ALL PARTS',
        'full updated ALL PARTS'[Month],
        'Part Numbers'[Part number]
    )
)

If you still help, please share some data sample which could reproduce your scenario and your desired output so that we could help further on it.

Best  Regards,

Cherry