Custom Column, DATEDIFF, TODAY....nothing works?

[MisterT]

So this is driving me a bit crazy.  Something that should be so simple is taking so long to solve and none of the forum suggestions I've seen work.

And I know I can solve this by adding columns into the underlying database (or adding views) but I don't want to do that and it kind of defeats the purpose of a BI tool surely.

So all I want to do is show the last x days of data in a graph.  None of the available filters provide this which was the first somewhat staggering discovery.

Anyway, back to the point.  There is lots of talk of creating a custom column and using some kind of data difference to provide a "days old" kind of solution.  And then filter on that column.  Sounds reasonable. Except nothing seems to work.  I'm using import queries from a SQL database and I use the following when I add a custom column:

New Column Name

DaysOld

Custom column formula:

= DATEDIFF(TODAY(), [EmailDate] , DAY)

I get a nice but clearly useless message saying that "No syntax errors have been detected" but when I click OK I get an error saying "Expression error: The name 'DATEDIFF' wasn't recognised.  Make sure it's spelled correctly."

So then I think, lets ready about Power BI Desktop formulas and try something simple!  So I create a custome column with just the following in it:  = DateTime.LocalNow

And it works - wonderful.  And then shows nothing but "Function" as the returned data in all data views and reports.  What?  Then I thought maybe it got the datatype wrong but I can't find any way to change it.

So for now, and like the other person in this forum that was trying to do soemthing similar, I will give up.  I'll do what I really did not want to do and create a view with the necessary data!  Hopefully someone can tell me that there is a simple solution to my incredibly simple requirement.  Fingers crossed 🙂

Thanks

[bazeemuddin]

I had the Same Problem,

I tried Just creating a new column like this

Num_Days = IF((Tickets[Status] = "CLOSED") || (Tickets[Status] = "RESOLVED"),

DATEDIFF('Tickets'[Reported Date], 'Tickets'[Actual Finish], DAY),

DATEDIFF(Tickets[Reported Date], TODAY(), DAY))

I know you already have a solution, the above one may help using IF and OR

Thanks for reading

Wish you the happiest time.

[drwinny]

@bazeemuddin, Just what I needed, thank you for the help 😀

[michael-hawker]

To get the number of days between now and a prior date, I used this:

Duration.Days(Date.From(DateTime.LocalNow()) - [Previous Date])

Seemed a lot simpler to just use the built-in Date type (make sure [Previous Date] is of type Date).

Edit: I had initially accidently subtracted one as I was comparing against Calculator, but there's currently an issue which is being fixed, see here: https://github.com/Microsoft/calculator/issues/178

[Anonymous]

I know this is a super old post but your solution worked where NOTHING else would. Spent WAY too much time on something so simple. THANK YOU!

[Afernandez]

I was facing the same issue and for me the best solution has been to calculate directly the substract of the two DateColumns with the Custom Column ( = DateB - DateA ).

It returns a column with the time difference. After that it is easy to transform to days, months or any other date type.

Example:

  Date A =02/01/2018 18:15:21

  Date B = 08/01/2018 6:35:46

  Returned Value = 5.12:20:25.0133334

[Eric_Zhang · Accepted solution]

@MisterT

You can try the below exprssion in DAX but not in Power Query.

elapsedDays = DATEDIFF( [EmailDate],TODAY() , DAY)

---

And I know I can solve this by adding columns into the underlying database (or adding views) but I don't want to do that and it kind of defeats the purpose of a BI tool surely.

---

Or even directly in the query, no need to add columns to the underlying table.

select emaildate,datediff(day,emaildate,getdate()) elapsedDays from testdb.dbo.tableDate

[trebgatte]

@Eric_Zhang Nice. Glad to have another way to do this.

Treb Gatte, Business Solutions MVP | @tgatte | Blog | CIO Magazine Blog | YouTube

[trebgatte]

Try this technique. It's based on the number lines we used to do in elementary school.

In a custom column, use the formula below, where YourDate should be type Date for the comparison. I'm also using -14 to set a point in time 14 days back for comparison. Note, you can easily substitute the -14 with the name of a parameter.

Value.Compare([YourDate], Date.AddDays(DateTime.LocalNow(), -14)

This will return a 1 for any date that is later than 14 days ago. 0 for equal to 14 days ago and -1 for anything earlier than 14 days ago. 

The advantage is that you can use this value in your individual visualization filters using the 1,0,-1 as you are looking for sets of data. So, if I want a graph of all data in the last 14 days, I'll set the filter in the graph visualization to = 1. Hopefully, this makes sense.

This link is your friend when doing this. https://msdn.microsoft.com/en-us/library/mt211003.aspx 

Hope this helps.

Treb Gatte, Business Solutions MVP | @tgatte | Blog | CIO Magazine Blog

[MisterT]

Hi and thanks for the response.  This custome column at least returns data but it doesn't work for me.

So it doesn't matter what value I put in to replace -14, I always end up with -1 as the result?  My date field is definitely formatted as a date in SQL Server and is appearing as a date type in PowerBI.  I've included a screenshot so you can see the formula and the result:

As for that link, it didn't show anything?

[trebgatte]

Whoops. I left out a step. It helps to look at one's notes. This is something I teach in my Advanced Power BI class normally.

In order for the comparison to work, you have to compare numbers not dates. So the trick is to convert the dates to their numeric representation. Once done, this works like a charm.

The formula becomes:

=Value.Compare(Number.From([YourDate]),Number.From(Date.From(Date.AddDays(DateTime.LocalNow(),-14))))

Documenting the results for later, when this is found via search.

-1 if YourDate is less than Now-14 : Past <------------(YD)-------------(Now-14)------------------> Future

0 if YourDate = Now - 14 :   Past <-------------------------(Now-14=YD)------------------> Future

1 if Your Date is greater than Now - 14 : Past <-------------------------(Now-14)---------(YD)--------------> Future

When you add a visualization to the Report/Dashboard, now you can add this custom flag in the Visual level filter and then filter for 1/0/-1 or some combination to get the appropriate set of dates, even though the date calculation is dynamic.  

You might need to use a different LocalNow() M command if you are sensitive to Timezone differences. There are some great articles on this that are easily found via search engine.

This link is the Power Query M Language reference. 

Now, I hope this helps. 

Treb Gatte, Business Solutions MVP | @tgatte | Blog | CIO Magazine Blog | YouTube