Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Small Multiples - Keep time series on X-axis while filtering on sales in past 12 months

Hi everyone,

Use case
I am trying to build a small multiples chart that only displays combinations of countries and products where there has been at least one sale in the past 12 months.



However, when I apply the below filter measure, the x-axis is automatically also filtered on the past 12 months. I want the x-axis to be unaffected by the filter and show the full time series.

To exemplify, I want the x-axis to display sales from 2019 for the combinations of countries and products that have had at least one sale between today and 2022-08-24.


VAR SalesPastYear =





    date [date] >= TODAY() - 360 &&

    date [date] <= TODAY()





IF(SalesPastYear >0,1,0)

Frequent Visitor

@amitchandak Thanks for getting back to me.

Based on your input and the youtube video I created the below filter measure. However, when I apply it as a filter to the visual, the x-axis in the small multiples still is filtered to the past 12 months. The purpose of the filter is to identify which combinations of countries and products have had any sales in the past 12 months, but the small multiples should still display the full date range. 

CountryProductsWithSalesPast12Months =

var _max = maxx(allselected(Date2), Date2[Date])

var _min = eomonth(_max, -12) +1

var TotalSalesLast12Months =

calculate( [Sales], filter(Date, Date[Date] >=_min && Date, Date[Date <=_max))





Super User
Super User

@joen12ac , if you want a trend there should not be a filter on the page. If there is a filter then it should be on an independent table


//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
calculate( sum(sales[Gross Sales]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))



Need of an Independent Date Table:


Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors