Forum Discussion

MV13's avatar
MV13
Frequent Visitor
8 years ago
Solved

TOP N filter across previous months based on current Month

Hi All   I am trying to create a report where the user uses a date slicer (Eg 01/01/2014 - 04/08/2015) to filter the report, I have a number of categories that I would like to display within a stac...
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    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 🙂

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
4 years ago

Hi SK87 

Sorry about the broken link! A change in my email address invalidated some of my older OneDrive links.

 

I have updated the link in the post above and also attached the file directly to that post.

 

Let me know if you have any issues. If you come across any of my other links that are not working, you can replace

owenauger_ozerconsulting_onmicrosoft_com

with

owen_owenaugerbi_com

Regards,

Owen 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi OwenAuger 

Thanks a lot for the solution. It helped me. I was struggling to resolve this problem for some days. But now I'm having a second problem. I am using this example to filter the top 10 also  based on the amount... but I want to show the % variation month by month, and when I change this part:

 

// Return Value Sum filtered to those Categories

RETURN CALCULATE ( [Value Sum], KEEPFILTERS ( TopCategories ) )

to this:

// Return Value Sum filtered to those Categories

RETURN CALCULATE ( [% variation month by month], KEEPFILTERS ( TopCategories ) )

 

the measure lose it self and go back to show all the categories.

Here is my [% variation month by month] calculation:

 

VAR vValueLastMonth =
CALCULATE(
    [Value Sum]
    ,PARALLELPERIOD( DIMDATA[Data], -1, MONTH )
)

VAR vVariation =
DIVIDE(
    [Value Sum]
    ,vValueLastMonth
)

RETURN
vVariation - 1
 
 
 
What am I doing wrong?
Thanks in advance.
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi OwenAuger,

    I have a third problem.

    I'm trying to use those measures inside a Field Parameter, that will switch between TopN based on [Value Sum] from current month (like your explanation) and [Value Sum] of all data, so I can choose with a field parameter, which one I prefer to see on the chart.

    I did some changes and it is like this:

     

    // Get Top 10 Categories in that month
VAR TopCategories =
    CALCULATETABLE (
        TOPN ( 10, ALL ( Data[Category] ), ALL ( 'Calendar Date'[Date] ) ) )
    )

     

    But it is filtering in a weird way, because there is not all categories, but there are more than 10. I think it is choosing the Top 10 from each month, and not from all data.