Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
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
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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
Here is a link to my new post if you can still help;
Thank you!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
61 | |
50 | |
45 | |
20 | |
17 |