Scenario: You need to configure a Power View report to show data for a dynamic date, such as the current date or the last date with data. However, as it stands Power View doesn’t support expressions.
Workaround: Add a Boolean calculated column to the Date table that returns TRUE for the date of interest. For example, if you want the report to show data for today’s date, the expression might be:
=if([Date]=TODAY(), True, False)
where [Date] is the column with date data type. Then, use this column as a filter in Power View.
Unfortunately, this workaround has a significant limitation. If the report needs another filter on the Date table, such as to allow the user to overwrite the default filter on the current date, this approach won’t work because filters on multiple attributes are interpreted as an AND condition (Date is 1/1/2013 AND TodayDate=True). Inability to specify OR filter condition between attributes is another Power View limitation. So, users needs to be trained to use either the TodayDate or Date filter but not both.
Looking ahead, it will be nice if Power View supports VB or DAX expressions as regular SSRS reports do.
Many thanks to Darren Gosbell for suggesting the workaround.