Forum Discussion
Calculate days between date columns
? where are you creating your measure/column?
it should look somehow like this where you enter it
it should recognize the function
you can also use iferror but its a bit forced sometimes and not always the most beautiful solution but having something working quick is most important sometimes
aprice7 you need to do that in dax not power query so close and load out of power query and do it in the front end of the model
The data I want to work with is the 'Due Date' column. It's a date field but has null values where the data was not entered. The 'Today's Date' column is one I added, thinking I might have more luck if I have column to deduct from the other. I want to know from the Due Date column how many days past due it is from today's date.
I've tried various formulas offered on different threads. For example, this was recommended elsewhere on the forum, however I get the error in yellow that I attached. I seem to get that error for any formula (DATESBETWEEN, IF etc)
There's also the DATEDIFF DAX function.
Days between = DATEDIFF([Transactiondate],[Closed],DAY)
I realise it's probably a very simple solution that I'm just missing right now. Thanks for any guidance!
(It's not letting me upload pictures right now :/) Will try my best to describe below...
Data I have:
Due Date Today's Date
28/03/2017 13/06/2017
null 13/06/2017
null 13/06/2017
null 13/06/2017
06/04/2017 13/06/2017
09/02/2017 13/06/2017
Error message I receive:
Expression.Error: The name 'DATEDIFF' wasn't recognized. Make sure it's spelled correctly.
The Add Custom Column I am writing (and using the available columns insert button to add Today's Date & Due Date):
=DATEDIFF([#"Today's Date"],[Due Date],DAY)
Resolver IV? where are you creating your measure/column?
it should look somehow like this where you enter it
it should recognize the function
you can also use iferror but its a bit forced sometimes and not always the most beautiful solution but having something working quick is most important sometimes
I am in Query Editor > Add Column > Custom Column. That is where I've been trying all the formulas I have seen on the blog related to this, but receiving the error I mentioned above.
- vanessafvg
Community Champion
aprice7 you need to do that in dax not power query so close and load out of power query and do it in the front end of the model