Forum Discussion
Using Today's date for aging calculation in custom column
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
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] ))
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!
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.
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
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
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] ))
m_dekorte, thanks for your continued assistance.
Chris