For this package of dashboards, the requirement was for each KPI visual to include the last 12 completed months. Additionally, the sales channel mix visual was to display the last six completed months. These two filter conditions ensure that only complete months are included and help to focus the dashboard on recent trends. 

The exclusion of the current month can be implemented via the Calendar Month Status described in the Date dimension view section of Chapter 2, Connecting to Sources and Transforming Data with M. However, to support the last 12 completed periods condition, an additional dynamic column (Trailing 3 Calendar Month Periods) was added to the SQL view of the date dimension. These two columns can be utilized as page-level filters in the reports containing the KPI visuals, per the following image:

Per the preceding page-level filter condition, the Trailing 3 Calendar Month Periods column contains eight custom trailing values representing 3-month intervals relative to the current month. For example, when the current month is December of 2017, the Trailing 1-3 Calendar Months value includes September, October, and November of 2017. Selecting four of the values as page-level filters ensure that each visual on the given report page will not contain more than the last 12 completed months.

The DATEDIFF() T-SQL function and the standard CURRENT_TIMESTAMP() SQL function is utilized within a case expression to produce the Trailing 3 Calendar Month Periods column. The logic for this additional column is embedded in the SQL view object accessed by the date dimension table of the Power BI dataset, per the following example:

CASE 
    WHEN DATEDIFF(MONTH,D.[Date],CAST(CURRENT_TIMESTAMP AS date)) = 0 
        THEN 'Current Calendar Month'
    WHEN DATEDIFF(MONTH,D.[Date],CAST(CURRENT_TIMESTAMP AS date)) IN (1,2,3) 
        THEN 'Trailing 1-3 Calendar Months' 
    WHEN DATEDIFF(MONTH,D.[Date],CAST(CURRENT_TIMESTAMP AS date)) IN (4,5,6) 
        THEN 'Trailing 4-6 Calendar Months'
    WHEN DATEDIFF(MONTH,D.[Date],CAST(CURRENT_TIMESTAMP AS date)) IN (7,8,9) 
        THEN 'Trailing 7-9 Calendar Months'
    WHEN DATEDIFF(MONTH,D.[Date],CAST(CURRENT_TIMESTAMP AS date)) IN (10,11,12) 
        THEN 'Trailing 10-12 Calendar Months'
    WHEN DATEDIFF(MONTH,D.[Date],CAST(CURRENT_TIMESTAMP AS date)) IN (13,14,15) 
        THEN 'Trailing 13-15 Calendar Months'
    WHEN DATEDIFF(MONTH,D.[Date],CAST(CURRENT_TIMESTAMP AS date)) IN (16,17,18) 
        THEN 'Trailing 16-18 Calendar Months'
    WHEN DATEDIFF(MONTH,D.[Date],CAST(CURRENT_TIMESTAMP AS date)) IN (19,20,21) 
        THEN 'Trailing 19-21 Calendar Months'
    WHEN DATEDIFF(MONTH,D.[Date],CAST(CURRENT_TIMESTAMP AS date)) IN (22,23,24) 
        THEN 'Trailing 22-24 Calendar Months'
    ELSE 'Older Periods'
  END AS [Trailing 3 Calendar Month Periods]

The Date dimension view section of Chapter 2, Connecting to Sources and Transforming Data with M includes additional details on the SQL view for the date dimension table. This includes the WHERE clause (filter condition) and the Calendar Year and Calendar Month Status columns. Filter conditions associated with the report, page, and visual-level filters were described in the Report filter scopes section of Chapter 5, Creating and Formatting Power BI Reports.