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.
Hi all,
I am an intermediate dax user but Im struggling to figure out how to do this.
I have a table (call it Sales) with part number and sale transaction date. I have a second table (call it Pricing) with a list of part numbers and MSRP selling prices with start dates and end dates. So, in this second table, I have part numbers listed several times with date ranges (start dates and end dates) for each new price that they have had over time. This creates a many to many relationship if I try to connect part number in the two tables.
What I want to do is create a calculated column in the Sales table that looks at the Pricing table and picks the MSRP for the date range which the transaction date is in. In other words, for a given line in the Sales table, take the part number and the transaction date, and look in the Pricing table for the part number and the start date and end date range that contains the transaction date and report back to the column the MSRP that from the Pricing table that meets that criterion.
here is what I came up with, but it doesnt work
Solved! Go to Solution.
Here's what worked for me:
I have a Product Pricing table with ProductKey, StartDate, EndDate, and Price.
I have a Sales table that has ProductKey and Date.
In the Sales table I created the following column:
Here's what worked for me:
I have a Product Pricing table with ProductKey, StartDate, EndDate, and Price.
I have a Sales table that has ProductKey and Date.
In the Sales table I created the following column:
thats perfect... thank you!!
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |