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

[joen12ac]

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.

Challenge

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.

Measure

VAR SalesPastYear =

CALCULATE(

    [Sales],

    FILTER(

    date,

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

    date [date] <= TODAY()

),

ALL(date)

)

RETURN

IF(SalesPastYear >0,1,0)

[joen12ac]

@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))

RETURN

IF(TotalSalesLast12Months>0,1,0)

[amitchandak]

@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
return
calculate( sum(sales[Gross Sales]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI