topic Re: DAX Measure : SUMIF Next Sale Month not equal to Transaction Month in DAX Commands and Tips

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

l0ganBI — Wed, 03 Feb 2021 00:47:00 GMT

Hello,

I have three tables

Inventory

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!!

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

edhans — Wed, 03 Feb 2021 01:03:50 GMT

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.

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

wdx223_Daniel — Wed, 03 Feb 2021 01:10:02 GMT

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

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

l0ganBI — Thu, 04 Feb 2021 23:59:17 GMT

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.

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

l0ganBI — Fri, 05 Feb 2021 00:03:57 GMT

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.

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

l0ganBI — Fri, 05 Feb 2021 01:17:03 GMT

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-equal-between-two-date/td-p/1646636

Thank you!