Forum Discussion
Custom Column, DATEDIFF, TODAY....nothing works?
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
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 :smileyhappy:
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!