Is it possible to include only a selected year and 4 years in the past?

[Anonymous]

Would it be possible to have a filter on a visualization which allows you to select a year and includes data for the last 4 years? Like for example, if I select 2016, the visualization will include data for 2016, 2015, 2014 and 2013.

[v-haibl-msft]

@Anonymous

Not sure what does the final visual chart like. But you can try with following steps to see if it is you expected.

1. Create a calendar table if not exists and add two columns of YearMonth and Year. Create relationship with the fact table.
   

   YearMonth = 
   YEAR ( 'Calendar'[Date] ) * 100
   + MONTH ( 'Calendar'[Date] )

   Year = 
   YEAR ( 'Calendar'[Date] )

2. Create a Year table with following formula. It will list the distinct years. Do not create relationship with other tables.
   

   Year = 
   VALUES ( 'Calendar'[Year] )

3. Create a Year column in the fact table.
   

   Year = 
   YEAR ( Table1[Date] )

4. Create a measure to calculate something like total sales.
   

   TotalSales = 
   VAR LastDay =
       DATE ( MAX ( 'Year'[Year] ), 12, 31 )
   VAR FirstDay =
       EDATE ( LastDay, -48 )
   VAR TotalSales =
       SUM ( Table1[Sales] )
   RETURN
       IF (
           MIN ( 'Calendar'[Date] ) < FirstDay,
           BLANK (),
           IF ( MIN ( 'Calendar'[Date] ) > LastDay, BLANK (), TotalSales )
   )

5. Use the column chart and slicer (use the 'Year'[Year] column) to show the result.
   
   
   

Best Regards,

Herbert

[Meagan]

This is possible.  For example, if I have sales data and I want to choose a year and see the last four years, my calculation might be. You just have to make use of CALCULATE and possibley FILTER. This also depends on whether you want just whole years or if you mean rolling 12 month periods. If you mean rolling periods, you would have to alter your formula and you could probably use DATESBETWEEN instead of the FILTER function in the calculation below.

Last 4 years = CALCULATE(Sum('FactInternetSales'[SalesAmount]), Filter(All('DimDate'),'DimDate'[CalendarYear] <= Max('DimDate'[CalendarYear]) && 'DimDate'[CalendarYear] > MAX('DimDate'[CalendarYear]) - 4))