Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Carnage
Frequent Visitor

Help with DAX formula

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.

 

https://ibb.co/haiLLx

1 ACCEPTED SOLUTION
afzalphatan
Resolver I
Resolver I

Hope the below calcualted column formula help....

 

Mark the ans if ur requirement is met. 

 

Pic.PNG

View solution in original post

6 REPLIES 6
afzalphatan
Resolver I
Resolver I

Hope the below calcualted column formula help....

 

Mark the ans if ur requirement is met. 

 

Pic.PNG

@afzalphatan

 

Thank you! Had to modify slightly to work in PowerBi but I got the results I was looking for. 

@Carnage  what changes did you have to make to get this working?

afzalphatan
Resolver I
Resolver I

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)

 

 

Excel Array formula.PNG

 

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

stretcharm
Memorable Member
Memorable Member

 

@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/

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.