DAX Measure : SUMIF Next Sale Month not equal to Transaction Month

[l0ganBI]

Hello,

I have three tables

Inventory

Id

0001

0002

0003

Sales

Inventory Id		Sale Date		Quantity
0001		10/01/20		15
0002		10/02/20		10
0003		10/03/20		20
0001		10/04/20		5
0002		09/02/20		5
0003		10/05/20		20
0001		11/11/20		15

Transaction Log

Inventory Id		Transaction Date
0001		09/30/20
0002		09/29/20
0003		10/01/20
0001		10/02/20
0002		08/31/20
0003		10/01/20
0001		10/31/20

Here are my relationships

Inventory     Sales

Id   1:* Inventory ID

Inventory   Transaction log

Id   1:* Inventory ID

How do I write a measure that will give me the total quantity sold where the transaction month is not equal to the following sale month? I want the result to be this:

Inventory Id		Quantity	Transaction Date		Sale Date
0001		15	09/30/20		10/01/20
0002		10	09/29/20		10/02/20
0002		5	08/31/20		09/02/20
0001		15	10/31/20		11/11/20

Thank you in advance for any help or guidance!!

[wdx223_Daniel]

you can not get what you want depending on the sample data.

a key column should be add in the sales table and transaction log table to defind for each row in sales which row of the transaction log table is its correspondence

[l0ganBI]

Thanks for your recommendation @wdx223_Daniel. I managed to rebuild my data model where it includes the transaction date and sale date in the same table.

[edhans]

I'm not sure I fully understand your question @l0ganBI - take a look at this:

This measure returns the quantity where the current month isn't equal to next month, but it requires time intelligence and thus a date table. 

Total Quantity = 
VAR varCurrentMonth = MAX('Date'[Month Year Sort])
VAR varNextMonth = 
    CALCULATE(
        MAX('Date'[Month Year Sort]),
        NEXTMONTH('Date'[Date])
    )
VAR RESULT = 
    CALCULATE(
        SUM(Sales[Quantity]),
        FILTER(
            'Date',
            'Date'[Month Year Sort] <> varNextMonth
        )
    )
RETURN
RESULT

My PBIX file is here if you want to take a look at it.

[l0ganBI]

@edhans 

Here is a link to my new post if you can still help;

https://community.powerbi.com/t5/DAX-Commands-and-Tips/SUM-values-in-table-where-months-are-not-equa... 

Thank you!

[l0ganBI]

Hello @edhans . Thank you for your reply and I apologize if my post is not very clear. I managed to get the transcation date and the sale date columns into the same table which should help clarify what I am trying to accomplish. I will repost this and tag you if you wish to continue helping me out. Your measure looks very close to what I need but does not yet give me the result I need.