powerbidaxssas-tabularazure-analysis-servicesdatefilter

SSAS Tabular Model & Power BI - Week filtering in report


I created an SSAS Tabular Model in VS2017 and the model has a Date Table.

Model UML Reference

In the Data Table, I have measures to obtain the current week of the selected day (for my model, always TODAY()), and the weeks start on Monday and end on Sunday.

In my Power BI report I want to filter an entire page by the current week but if I use the "Relative Data Filtering" coming along with Power BI, the week starts at Sunday and ends on Monday. This leads to an error in the data display.

Relative Data Filtering in Power BI

Now, the SSAS Tabular Model is deployed to an Azure Analysis Services server, and the Power BI report connects to this. In this case, I am not able to configure in Power BI any regional settings.

How can I filter an entire report page by the formatted week I have?

Thanks in advance.


Solution

  • Similar to this question, you can create a calculated column on your calendar table, Dates, to determine which dates to include or exclude.

    Dates[IncludeDate] = IF( Dates[Date] >= [StartOfWeek] && Dates[Date] <= [EndOfWeek], 1, 0 )
    

    Then use Dates[IncludeDate] is 1 as your filter.