cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frajzer
New Member

Connection between date and date range

Hello,

My task is to calculate price of a material based on a shipment date.
As price in the past changed several times thus I have to connect single date of shipment with material price from that period of time. In my shipment table I have single column with dates but in material costs table I have two columns with dates (Valid from/ Valid to). Is there anyway to connect these dates somehow ?


1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @Frajzer 

 

Suppose you have two tables like below and you want to add a price column to the Shipment table according to the prices in Costs table based on a corresponding price period.

042803.jpg

 

You can copy and paste below codes into a blank query's Advanced Editor to create a function. I call this function FindPrice. It will find a price from Costs table according to a provided parameter OrderDate.

(OrderDate)=>
let
    Source = Costs,
    Custom1 = Table.SelectRows(Source,each [Valid From] <= OrderDate and [Valid To] >= OrderDate){0}[Price]
in
    Custom1

 

In Shipment table, click on Invoke Custom Function to add a column and use the FindPrice function and Ship Date column to invoke this function to get the prices.

042804.jpg

Hope this helps.

Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @Frajzer 

 

Suppose you have two tables like below and you want to add a price column to the Shipment table according to the prices in Costs table based on a corresponding price period.

042803.jpg

 

You can copy and paste below codes into a blank query's Advanced Editor to create a function. I call this function FindPrice. It will find a price from Costs table according to a provided parameter OrderDate.

(OrderDate)=>
let
    Source = Costs,
    Custom1 = Table.SelectRows(Source,each [Valid From] <= OrderDate and [Valid To] >= OrderDate){0}[Price]
in
    Custom1

 

In Shipment table, click on Invoke Custom Function to add a column and use the FindPrice function and Ship Date column to invoke this function to get the prices.

042804.jpg

Hope this helps.

Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

View solution in original post

artemus
Microsoft
Microsoft

Something like:

 

Table.AddColumn(myTable, "OrigionalCost", (myTableRow) => Table.SelectRows(ShipmentTable, (shipRow) => shipRow[ValidTo] <= myTableRow[OrderDate] and shipRow[ValidFrom] > myTableRow[OrderDate]){0}?[Cost])

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors