Power View Date Filter Troubles

Now that I’m doing some more development of dashboards in the Power View interface, I thought it might be helpful to post about some of the date “gotchas” I’ve run in to with the development interface.  Power View is very nice about handling Date filters if you are able to specify a date you’d like to use as a start point, end point, or 2 dates to post data about between, however, if you’re looking to determine if a date range is say, “This Week”, “This Month”, “This Quarter”, etc… you’ll have quite a bit more trouble.  The workaround I’ve found to solve this issue is to create a new column that results in a boolean (true/false) answer.  Then within your Power View filtering, you are able to choose “IsPastWeek” = True, etc.  For Example, in your formula bar (or whatever tool you’re using, excel, powerbi, or SSAS tabular):

The above formula, simply put, says If the date is later than today minus 7 days, then return true, else, return false.  Below is a view of the filters in my Power View Dashboard:

By setting this filter, the ability to look at specific dates or time periods makes it easier to report graphically.  Also, the ability to filter on separate objects within the same canvas allows us to easily compare similar time periods side by side.

Some other options for comparing date ranges are:

IsCurrentMonth =if(MONTH(Table[Date])=MONTH(NOW()),TRUE,FALSE)

IsCurrentYear =if(YEAR(Table[Date])=YEAR(NOW()),TRUE,FALSE)

And many more can be found here: Time Intelligence in Power Pivot in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.