cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Carnage Frequent Visitor
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

Accepted Solutions
afzalphatan Member
Member

Re: Help with DAX formula

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
stretcharm Senior Member
Senior Member

Re: Help with DAX formula

 

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

 

afzalphatan Member
Member

Re: Help with DAX formula

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 

Carnage Frequent Visitor
Frequent Visitor

Re: 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

afzalphatan Member
Member

Re: Help with DAX formula

Hope the below calcualted column formula help....

 

Mark the ans if ur requirement is met. 

 

Pic.PNG

View solution in original post

Carnage Frequent Visitor
Frequent Visitor

Re: Help with DAX formula

@afzalphatan

 

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

Highlighted
jcarville Established Member
Established Member

Re: Help with DAX formula

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 422 members 3,911 guests
Please welcome our newest community members: