Forum Discussion

CRyley's avatar
CRyley
New Member
3 years ago
Solved

Using Today's date for aging calculation in custom column

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.

Need Help
  • CRyley's avatar
    CRyley
    3 years ago

    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's avatar
    m_dekorte
    3 years ago

    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]  ))

10 Replies

  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    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's avatar
      CRyley
      New Member
      3 years ago

      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's avatar
        m_dekorte
        Resident Rockstar
        3 years ago

        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!