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