TopN and 'Other' with date filter measure

[badger123]

Hello,

I'm trying to calculate top n and 'other'. I've followed these steps: https://www.minceddata.info/2018/06/06/topsomething-and-all-the-rest-called-other/. This works (see my measure below) but I want to add another layer of complexity.

The goal is to have a line chart showing monthly values over the past 4 years for the top 5 categories and 'other' categories. However, I want the top 5 to be based on the category values over the past 3 months. Does that make sense? How can I achieve this? I'm have a date index for relative date filtering.

Top 5 categories = 
var top5 = CALCULATETABLE(TOPN(5,VALUES(categories[category]),CALCULATE(SUM('values'[value]))))
var other = ROW("Category", "Other")
var allTheRest = CALCULATE(SUM('values'[value]), EXCEPT(VALUES(categories[category]),top5))
var theUnion = UNION(top5,other)
return
SUMX(
    INTERSECT('Unrelated',theUnion)
    ,
    var currentIterator = 'Unrelated'[categories and other]
    return
    IF(
        'Unrelated'[categories and other] <> "Other"   
        ,CALCULATE(
            SUM('values'[value])
            ,'categories'[category] = currentIterator
        )
        ,allTheRest
    )
)

[v-juanli-msft]

Hi @badger123 

Do you want to get a bar chart as below:

For every month, show all top 5 categories, others show as "others".

Or you want a visual as the link shown which only has month.

Best Regards

Maggie

[badger123]

Hi @v-juanli-msft ,

Thanks for the quick response. I want to create a line chart, not a bar chart. Please see below.

Except I don't want to each month to show the top 5 categories each month... I want the whole thing to be based on the top 5 based on the values over the most recent 3 months (i.e. in this case Dec 2019, Jan 2020, Feb 2020... or 46, 47, 47 in index terms)... if that makes sense?