Calculate days between date columns

[aprice7]

I've read a lot of question/answers on the forum about this topic and cannot get any of the solutions to work.

I have a date column where I need to determine if the dates are past due from today's date. Specifically how many days past due they are. I.e. if the date is 10th June 2017 then the calculation would determine the row is past due by 3 days (today being 13th June 2017).  Some of the rows have no data (null value as the data was not completed in the database) and some of the rows will be within the due date range.

I've tried various formulas I have read on the blog but consistently get errors like 'DATEDIFF is not recognized' etc when I click OK on the Custom Column set-up screen. I created a new column where I entered today's date for all rows thinking it would be easier to calculate if I have 2 date columns to compare. Ideally the formula wouldn't need this additional column to calculate.

Any ideas or help you can give? I do not currently have access to Power Pivot in Excel, so hoping to complete this within Power BI if possible. I realise that the 'null' values in the column probably doesn't help the situation.

Thanks,

Alex

[cs_skit · Accepted solution]

? 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

[vanessafvg]

@aprice7 i dont understand how datediff isnt' recognized when its a valid function in dax, where are you doing this exactly?  please put screenshots of your data and show what it is your wanting to do.

[aprice7]

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)

[cs_skit · Accepted solution]

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

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 · Accepted solution]

@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