Using Today's date for aging calculation in custom column

[CRyley]

Hi,

I'm a complete newbie to this community and Power Query in general.

I'm trying to calculate aging (difference between today's date and one of two date field columns, as follows. If there is a value in column [Revised Implementation Date]  I want to use today's date - that value. If the value in [Revised Implementation Date] is null, then I want to use today's date - [Original Estimated Implementation Date].  This is Power Query Editor for Excel, and I'm hoping that code for PowerBI and Excel would be interchangeable.

My research has uncovered references to using M, but I'm not much of a programmer (VBA Macros in Excel are my comfort zone).

Any help would be appreciated. Thanks.

[CRyley · Accepted solution]

I was able to figure this out on my own. I did a bit more research on using M and also discovered the Add Conditional Column feature, which got me started on the if then else statement.

#"Added Conditional Column" = Table.AddColumn(#"Reordered Columns2", "Aging", each if [Revised Implementation Date] = null then Duration.Days(today-[Original Estimated Implementation Date]) else Duration.Days(today-[Revised Implementation Date]))

Thanks for getting me started along this path m_dekorte

Cheers

[m_dekorte]

Hi @CRyley 

Give this a go. You can copy the full script into a new blank query.

I've created a variable called today, this returns the current date from the system. Inside the calculation you see a double question mark, that applies coalesce to your date fields, returning the second if the first returns null.

let
    today = Date.From( DateTime.FixedLocalNow()),
    Source = Table.FromRows(
        {
            { #date(2023, 4, 15), #date(2023, 4, 16) },
            { null, #date(2023, 4, 12) }
        },
        type table [ Revised Implementation Date = date, Original Estimated Implementation Date = date]
    ),
    Result = Table.AddColumn( Source, "Result", each Duration.Days( today - (Date.From( [Revised Implementation Date] ) ?? [Original Estimated Implementation Date] )))
in
    Result

I hope this is helpful

[CRyley]

Thanks very much m_decorte!

This returns results, but not quite what I expected.

Firstly, I'm hoping to be able to incorporate the today query/results into a custom column in an expsting query that I'm using to generate an Excel report:

Secondly, the query with the source data (i.e., the two fields used in the calculation) has 9 rows of data but the results of the today query only return 2:

How can I incorporte the today query into the main query and have it apply to all rows so that I get the aging for all records?

Thanks again for your help.

[m_dekorte]

Hi @CRyley,

First, so what where you expecting?

Second, yes you will need to transfer this logic into a custom column. The sample provided is just to illustrate how the code works as you didn't supply a sample of your own.

Here are the steps to incorporate this logic.

Go to your excisting query and open the advanced editor. From the sample provided, copy the first line of code and paste that above the first line of code in your query. That's this bit.

today = Date.From( DateTime.FixedLocalNow()),

Close the advanced editor and use the user interface to add a custom column. In the dialog, give that new column a proper name and copy this bit into the code section.

Duration.Days( today - (Date.From( [Revised Implementation Date] ) ?? [Original Estimated Implementation Date] ))

If your column names do NOT match, select that field reference in the code section, delete it and double click the correct field from the field list on the right  hand side to replace it. 

Done.

Again, know that the today variable will return the current date from the system upon query refresh.

Please mark this answer as solution when it helped you to resolve your question, thanks!

[CRyley]

Thanks again m_dekorte.

Regarding your comment on my statement regarding the results not being quite what I expected, your suggested today query appeared to use my source data of 9 rows, but only returned 2 rows. I would have thought that if it returned 2 rows of the 9 it would have return more. That said, I'm not sure what the query was intended to return so likely my expectations where wrong.

So, I've added what I assume is your variable definition statement to the query, and no syntax errors were detected

(I initially made an error due to use of date.From instead of Date.From. The error messages helped me sort this out.)

Then, I created the new column and added your suggested code to the formula:

The resulting column returns one value (where there is a value in the [Revised Implementation Date] field) and Errors for the other rows (where the revised date is null):

This makes sense, and is getting me closer to what I need. However, I need to build in logic so that if there is a null value in the [Revised Implementation Date] field/column that the formula will then use the [Original Estimated Implementation Date] value. I can't figure out how to incorporate logic into the formula field comparable to a typical Excel If/Then statement such as: If([Revised Implementation Date]=0,today-[Original Implementation Date],today-[Revised Implementation Date]).

How do If/Then statements work in Power Query Editor and where to I use them?

Thanks again

[m_dekorte]

Hi @CRyley,

I think you are very, very close...

There is one step you didn't repeat back in your actions. Let's start there... 

When you click off to the side in the whitespace beside any of the errors does the say:

Expression.Error: The field 'Original Estimated Implementation Date' of the record wasn't found?

If it does, press the gear wheel on the Add Custom Column step to open the dialog

delect that field reference [Original Estimated Implementation Date], look through the field list on the right hand side to select the correct column name, double click it to enter it in the code and press done.

Please mark this answer as solution when it helped you to resolve your question, thanks!

[CRyley]

Thanks again.

When I click in the whitespace where I'm getting an error, I get:

"Expression.Error: We cannot convert the value #date(2019, 12, 31) to type Duration.
Details:
Value=12/31/2019
Type=[Type]

Any suggestions would be welcome.

Thanks.

[m_dekorte · Accepted solution]

Hi @CRyley 

Great you found a solution on your own!

With the error message I could identify the problem with the code above you were missing a set of parenthesis. That needed to be wrapped around Date.From and the end of the expression, like below.

Duration.Days( today - ( Date.From( [Revised Implementation Date] ) ?? [Original Estimated Implementation Date]  ))

[CRyley]

m_dekorte, thanks for your continued assistance.

Chris

[CRyley · Accepted solution]

I was able to figure this out on my own. I did a bit more research on using M and also discovered the Add Conditional Column feature, which got me started on the if then else statement.

#"Added Conditional Column" = Table.AddColumn(#"Reordered Columns2", "Aging", each if [Revised Implementation Date] = null then Duration.Days(today-[Original Estimated Implementation Date]) else Duration.Days(today-[Revised Implementation Date]))

Thanks for getting me started along this path m_dekorte

Cheers

[CRyley]

I was able to figure this out on my own. I did a bit more research on using M and also discovered the Add Condition Column feature, which got me started on the if then else statement.

#"Added Conditional Column" = Table.AddColumn(#"Reordered Columns2", "Aging", each if [Revised Implementation Date] = null then Duration.Days(today-[Original Estimated Implementation Date]) else Duration.Days(today-[Revised Implementation Date]))

Thanks for getting me started along this path m_dekorte

Cheers