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
Most Valuable ProfessionalTry 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
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?