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 🙂

MV13's avatar
MV13
Frequent Visitor
8 years ago

Hi v-xjiin-msft

 

Thank you for your response, I have tried your solution based on the same data that you have used.  From what I understand this gives me the top N for each month, this is useful however, what I am trying to visualise is how the top N in the latest period has performed over previous periods, so in this example, the top N for January 2018 is Category 1,2,3 and 5. So in my visual, I should only see categories 1,2,3 and 5 across the previous months, i.e Category 4 should not appear in December 2017.

 

 

Note: For any given month, a category can appear multiple times, I have a total of 300+ categories.  In addition to date, I am slicing this date by the Plant (Factory) that it comes from, as well as the Area within the factory. 

 

CategoryValueDatePlantArea
Category 10.512/01/2016Plant 1A1
Category 20.412/05/2016Plant 1A2
Category 20.512/05/2016Plant 2A3
Category 30.612/21/2016Plant 1A1
Category 40.112/28/2016Plant 1A3
Category 50.812/18/2016Plant 1A1
Category 10.21/2/2017Plant 1A2
Category 20.31/15/2017Plant 1A4
Category 30.11/12/2017Plant 1A2
Category 4 0.81/19/2017Plant 1A1
Category 50.11/25/2017Plant 1A3

 

I will try to dummy up some of the actual data so that it is easier for you to understand my issue. For some context, I am looking at the duration of downtimes in a plant(Value) and the cause of the downtime (Category). 

 

Thanks

 

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
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 🙂

  • SK87's avatar
    SK87
    Icon for Helper III rankHelper III
    4 years ago

    Hi OwenAuger 

     

    Could you please share the PBIX file instead of link so that I can see the solution as I am facing same problem.

    • SK87's avatar
      SK87
      Icon for Helper III rankHelper III
      4 years ago

      Hi OwenAuger Is it possible for you to share PBIX file here instead of link. I need to review your file as I am not able to understand how have you calculated 'Max Date Option'.

      I am stuck with same problem.

      Thanks in advance

      • 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 

  • sarochch's avatar
    sarochch
    Frequent Visitor
    3 years ago

    Hi @OwenAuger 

    Thanks. It helped me a lot but I have another question.
    How to create a Bottom N like your solution?

     

    • OwenAuger's avatar
      OwenAuger
      Icon for Super User rankSuper User
      3 years ago

      Hi sarochch 

      TOPN can also be used to return the "bottom N" items, by including an Order argument equal to ASC.

      In the example above, it would be the 4th argument of TOPN:

      TOPN ( 10, ALL ( Data[Category] ), CALCULATE ( [Value Sum], MaxMonth ), ASC )

      If the Order argument = DESC (default), values are sorted largest to smallest.

      If the Order argument = ASC, values are sorted smallest to largest.

       

      Regards,

      Owen