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
Anonymous
Not applicable

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

 

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

 

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.

artemus
Employee
Employee

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
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.

Top Solution Authors
Top Kudoed Authors