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 have a problem that seems relatively easy to solve but I can't seem to formulate the correct DAX expression. Here's what I'm trying to accomplish:
Table 1 contains transaction data with a Part number, Net price and Transaction date.
Table 2 contains Part number, Price effective date and List price
I'm trying to pull in the list price from table 2 into table 1 by transaction date and by not violating the price effective date ranges in table 2. In other words if a price effective date is between 1/1/2016 - 6/30/2016 and a transaction date falls within that period, I want the List price from that period. If it falls outside of that date range I want the new List price from the new effective date. I hope that makes sense. Hopefully the example below is a little clearer.
Solved! Go to Solution.
Hope the below calcualted column formula help....
Mark the ans if ur requirement is met.
Hope the below calcualted column formula help....
Mark the ans if ur requirement is met.
Thank you! Had to modify slightly to work in PowerBi but I got the results I was looking for.
Hi,
We can achieve ur requirement through Excel array and also DAX formula....
If your data set is small you can go for Excel Array formula... Below Pic has the formula (do not forget to press Ctrl+Shift+Enter)
We can also author DAX formula.....let me know if Array formula isn't enough for you data set.. I shall help with DAX formula
Thank you both for your replies.
@afzalphatanI am grateful for your solution in Excel, but my dataset is very large and would need to be run through PowerBi. What would the formula look like in DAX? Thanks again
@AlbertoFerrarihas a greate video that explains dax optimisation and he has an example similar to your problem for an open order calculation. He shows a couple of ways to solve this efficiently. Its worth watching it all, but the open order section starts at 49mins.
https://www.sqlbi.com/tv/dax-optimization-examples/
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 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |