Skip to content
Home » Create custom function to get two dates difference

Create custom function to get two dates difference

This article demonstrate how to create a custom function in Power Query that returns the difference of two dates.

Custom function takes a set of input from users and based on that input it evaluates the M code logic and return a single value.




Creating a custom function is useful, if have any logic that you want to reuse many times. You can simply create a custom function and can invoke it where ever you need it.

As you can see, here we have a table which contains the customer’s order details.

 

Let’s create a function  named OrderAging which return the difference of Order Date and Delivery Date.

For this, right click on table then select Create Function.. from context menu as shown below.

You might receive a warning indicating that query does not reference any parameters. You just need to click on Create button.

After that, a pop up window named Create Function appears which ask you to provide the name for your function, just provide the function name as shown below.

After that click on OK button, you will see that a function named OrderAging is created.




Now just select the function and click on Advance Editor, here we will write the logic for function.

Once you click on Advance Editor, a Edit Function window appears just click on OK button.

Now write the below function code.

(OrderDate as date, DeliveryDate as date) =>
let 
source = Duration.Days(DeliveryDate - OrderDate)
in 
source

Once you done with this, you can see now the function have two parameters, one for Order date and another for Delivery date.

Next, we will create a custom column which will use function to show the order date and delivery date difference.

Let’s create a customer column. First, select CustomerOrder table then click on Add Column tab after that click on Custom Column tab as shown below.

Now, you will see a Custom Column window appear. Now you need provide the name for column and write the M code for custom column as shown below.

=OrderAging([OrderDate], [DeliveryDate])

Once you done with this, click on OK button.




Now you can see that it returns the Order Aging, a day difference between Order date and Delivery date.

Also Read..

Create and invoking Custom Function

 179 total views,  3 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.