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.

I accept that my given data and my IP address is sent to a server in the USA only for the purpose of spam prevention through the Akismet program.More information on Akismet and GDPR.

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