Creating an age bucket using a custom function

[DataSkills]

Hello, 

I have a need to calculate age buckets for a variety of tables. 

So what I have done is:

1) Created a custom query called "TodaysDate" to get today's date:

let

Source1 = Date.From(DateTime.LocalNow())

in

Source1

meta [IsParameterQuery=true, Type=date, IsParameterQueryRequired=true]

2) I then created a function as follows:

(EndDt as date, StartDt as date) =>

let

    EndDt =TodaysDate,

    Age = Duration.Days(StartDt, EndDt)

in

Age

When I invoke the function, 

I get...

I am attempting to reuse this same Duration.Days(StartDt, EndDt) so that I can simplify creating custom columns as per this example:

if Duration.Days(TodaysDate - [CreatedDate]) <=14 then "New"

else if [LastVerifiedDate] is null then "Never Verified"

else if Duration.Days(TodaysDate - [LastVerifiedDate]) <= 30 then "Healthy"

else if Duration.Days(TodaysDate - [LastVerifiedDate]) <= 30 then "Healthy"

else if Duration.Days(TodaysDate - [LastVerifiedDate]) <= 79 then "Moderate"

else if Duration.Days(TodaysDate - [LastVerifiedDate]) >79 then "Aged"

else

"Other"

Thank you for assistance. 

[Vijay_A_Verma · Accepted solution]

Duration.Days needs to have only one parameter.

You will need to replace it with

Duration.Days(EndDt-StartDt)

Also note, you need not use EndDt as date in your function as anyway you are getting the value from another query. This is redundant. Below would work perfectly

(StartDt as date) =>
let
    EndDt =TodaysDate,
    Age = Duration.Days(EndDt - StartDt)
in
Age

The above query can be simply written as

(StartDt as date) => Duration.Days(TodaysDate - StartDt)

[DataSkills]

Thank you Vijay! I am annoyed at myself for missing that obvious error! I was looking in the wrong place!

Your improved code also helps so thank you for that too. 😎