Simple Date Filtering when Multiple Contexts are Required

[matgrisham]

I've created an SSAS tabular model and Power BI reports deployed to the service to allow our business users analyze sales. Within my model, I created calculated columns on the Date dimension to determine what the current week is so that my reports will auto-update to the next reporting week without any input from users (the current fiscal year and period are also marked). Using those three columns as filters in Report, Page, and Visual Level filters allows me to build some nice reports, but it becomes a challenge when users want to look at past dates.

For example, I have a report page that shows the Week, Period to Date, and Year to Date sales by our different Brands in a Matrix and a Line Chart that shows sales by period for the year. The page will have a filter for Current Year = Y so that only the current year is shown and the matrix will have a visual level filter for Current Period = Y. The problem arises when a user want to look at sales from the last period. They would have to turn off the Current Period filter on the matrix, and then filter by fiscal year, period, and week. This wold be further complicated if they wanted to look at last year or, as is the case in the real world, when there are more than two visualizations on the page.

Does anyone have an elegant solution to this problem, particularly when date filtering is required at different contexts within the page? My goal is to make this easy to use for business users that may not have the same level of understanding with Power BI that I do.

Thanks.

[MattAllington]

I would continue using what you have as a "snapshot" of the current state.  Then create another report for different periods.  What I have done in the past is to take a month column eg YYMM and the create a calculated column off this month column.  Return "Current Month" if it is the current month, or YYMM if it is not the current month.  That way they get the latest month all the time, but can go back and look at previous months too.

[matgrisham · Accepted solution]

@MattAllington I came up with a solution (using stuff I had learned from PowerPivotPro). I created disconnected slicers for Fiscal Year and Period that set the "current period" of the report page. Then, using CALCULATE, I created a Period measure and a Rolling 13 Period measure. It all comes together on the report when combining the rolling measure and the date dimension to show the trend over time. A full blog post would be needed to completely explain the method.