Calculating SUM where multiple conditions are met between tables without a relationship

[Anonymous]

I'm working a scrap report for a manufacturing company.

I have a table of transactions that show what material was scrapped including when it happened and how much was scrapped.

I have a table of materials cost data that details, for each material, how much of the cost is from variable costs, fixed costs, labor costs, etc.

Both the transactions table and materials costs table have active relationships to a Calendar table.

The cost data changes once a year, and on the same date every year, so I've "concatenated several" tables of costs data (using Get Data > Folder) and this new mega table has "DateValidFrom" and "DateValidTo" columns in it. I added them myself, that's the only way I could think of to deal with the cost data changing periodically; I needed some way to match a transaction with the cost data relevent when the transaction happened.

Because of that, materials will appear in my cost table mutiple times. Once with data for 2017, once for 2018, etc. That prevents me from making a relationship between the material number in the transactions table with the material number in the materials costs table, as the material number appears more than once in the materials costs table.

What I need to do is sum, for example, the "Fixed Cost" for every transaction for each material number where:

1. The material numbers matches in the transactions and costs tables.
2. The transaction happened on or after the "date valid from" in the materials cost table.
3. The transaction happened on or before the "date valid to" in the materials costs table.

This isn't the hard part, but I then need to multiply the sum of the fixed cost for that material by how many of them were scrapped in that transaction, and then divide that by the lot size for that material. This is because though only 2 of Bolt XYZ may have been scrapped, they are purchased in boxes of 100.

I'm having trouble with the DAX on this and have tried a number of combinations using CALCULATE(), RELATED(), USERELATIONSHIP(), etc. It's not even close to working, but since I'm in unfamiliar territory with DAX, I don't even know how to troubleshoot it.

Strangely, that ALLEXCEPT() function was the only way I could get it to (previously) sum the fixed costs for a single material number instead of all materials in the table.

Cost Fix = CALCULATE(
                        SUM(All_MaterialsCosts[Mfg.Overhead Fix]),
                        ALLEXCEPT(All_MaterialsCosts,All_MaterialsCosts[Material]),
                        AND(
                            All_Transactions[Posting Date] >= USERELATIONSHIP(All_MaterialsCosts[DateValidFrom],'Calendar'[Date]),
                            All_Transactions[Posting Date] <= USERELATIONSHIP(All_Transactions[DateValidTo], 'Calendar'[Date])
                            )
                        )
               * All_Transactions[Quantity]/RELATED(All_MaterialsCosts[Costing Lot Size])

Any suggestions on how to tackle this will be greatly apprecaited. Thanks for your time.

[Anonymous]

Here's another attempt:

Cost Fix = 
CALCULATE(
    SUM(All_MaterialsCosts[Mfg.Overhead Fix]),
    ALLEXCEPT(All_MaterialsCosts, All_MaterialsCosts[Material]),
    FILTER(All_Transactions, All_Transactions[Posting Date]) >= VALUES(All_MaterialsCosts[DateValidFrom]),
    FILTER(All_Transactions, All_Transactions[Posting Date]) <= VALUES(All_MaterialsCosts[DateValidTo])
    )

[Greg_Deckler]

Can you supply sample/example data, can't wrap my head around what is going here.

[Anonymous]

Yeah, I want to. It's just going to take me a little while to anonymize it. But, I'll work on it. Thanks for the interest in the meantime!

[v-yulgu-msft]

Hi @Anonymous,

Have you worked out your requirement bu yourself? If so, would you please share your solution to benefit more community members?

Regards,

Yuliana Gu