TOP N filter across previous months based on current Month

Hi MV13

Here's something I mocked up earlier in the day and just got home to post.

PBIX link

I set up a basic data model with a Calendar table and a Data table, with Data containing columns Date, Category, Value.

To calculate the sum of Value in any time period for the top 10 Categories (determined in the latest month), the measure I wrote has 3 parts:

1. Define the time period where the top 10 Categories will be determined (the 'max' month in this case)
   I added a parameter to choose between the max date filtered & the max date present in the data.
   Regardless, the max date is expanded out to a calendar month (in variable MaxMonth), and this time period is used to determine the top 10 Categories.
2. Determine the top 10 Categories in that month (stored in variable TopCategories)
3. Calculate sum of Value filtered to those categories.

Here is the actual measure (colour-coding matches above):

Value Sum For Top 10 Categories in Max Month = 
// Get Relative or Absolute selection
VAR MaxDateOption =
    SELECTEDVALUE ( 'Max Date Option'[Max Date Option] )
// Get Max Date
VAR MaxDate =
    SWITCH (
        MaxDateOption,
        "Latest Data", CALCULATE ( MAX ( Data[Date] ), ALL ( Data ) ),
        "Current Date Filter", CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
    )
// Expand this to a calendar month
VAR MaxMonth =
    CALCULATETABLE (
        PARALLELPERIOD ( 'Calendar'[Date], 0, MONTH ),
        'Calendar'[Date] = MaxDate
    )
// Get Top 10 Categories in that month
VAR TopCategories =
    CALCULATETABLE (
        TOPN ( 10, ALL ( Data[Category] ), CALCULATE ( [Value Sum], MaxMonth ) )
    )
// Return Value Sum filtered to those Categories
RETURN
    CALCULATE ( [Value Sum], KEEPFILTERS ( TopCategories ) )

Well, that's how I would approach it.

The part in red can be adjusted to whatever method you want to use to determine the 'max' month.

Regards,

Owen 🙂