Forum Discussion

heathernicole's avatar
heathernicole
Icon for Continued Contributor rankContinued Contributor
8 years ago
Solved

TopN Customers Rolling 12 months

Ok - So here's what I'm trying to recreate in Power BI (currently an excel file):     Logically, I get what's occurring in this report - however it's escaping me as to HOW to actually pull thi...
Need Help
heathernicole's avatar
heathernicole
Icon for Continued Contributor rankContinued Contributor
8 years ago

mattbrice - Hey -  I think this is right. :) I've modified a few things - but you gave me the core idea. THANK YOU!
I'm getting ready to meet with our Sales Manager to verify the numbers but from what I've checked so far it seems accurate.

 

THANKS SO MUCH! I'll post the final after I confirm. 

 

heathernicole's avatar
heathernicole
Icon for Continued Contributor rankContinued Contributor
8 years ago

Full answer with mattbrice base formulas from a test dataset -  I'm also including the measures for the Count of Customers. I redid the customers count - using the base that matt created. So shout-out to you, mattbrice! :) THANKS!

 

Count of Customers =
CALCULATE (
    DISTINCTCOUNT ( 'SALES DETAILS'[LinkToCustomerID] ),
    'SALES DETAILS'[SalesTxn Document Type] = "Invoice",
    'Calendar - Transaction Date'[Year] >= 2017
)
Count of Customers Last 12MO =
VAR _last12MO =
    DATESBETWEEN (
        'Calendar - Transaction Date'[Transaction Date],
        NEXTDAY (
            SAMEPERIODLASTYEAR (
                LASTDATE ( 'Calendar - Transaction Date'[Transaction Date] )
            )
        ),
        LASTDATE ( 'Calendar - Transaction Date'[Transaction Date] )
    )
RETURN
    CALCULATE ( [Count of Customers], _last12MO )
Total Outgoing Sales =
CALCULATE (
    SUM ( 'SALES DETAILS'[Sales $$] ),
    'Calendar - Transaction Date'[Year] >= 2017
)
Total Sales Last 12MO =
VAR _last12MO =
    DATESBETWEEN (
        'Calendar - Transaction Date'[Transaction Date],
        NEXTDAY (
            SAMEPERIODLASTYEAR (
                LASTDATE ( 'Calendar - Transaction Date'[Transaction Date] )
            )
        ),
        LASTDATE ( 'Calendar - Transaction Date'[Transaction Date] )
    )
RETURN
    CALCULATE ( [Total Outgoing Sales], _last12MO )
Total Sales Last 12 MO Rolling Top20 =
VAR _last12MO =
    DATESBETWEEN (
        'Calendar - Transaction Date'[Transaction Date],
        NEXTDAY (
            SAMEPERIODLASTYEAR (
                LASTDATE ( 'Calendar - Transaction Date'[Transaction Date] )
            )
        ),
        LASTDATE ( 'Calendar - Transaction Date'[Transaction Date] )
    )
VAR _Top20Last12MO =
    CALCULATE (
        SUMX (
            VALUES ( 'Calendar - Transaction Date'[Month] ),
            CALCULATE (
                [Total Outgoing Sales],
                TOPN (
                    20,
                    VALUES ( 'SALES DETAILS'[LinkToCustomerID] ),
                    [Total Outgoing Sales]
                )
            )
        ),
        _last12MO
    )
RETURN
    _Top20Last12MO

Final Result:

 

  • mattbrice's avatar
    mattbrice
    Icon for Solution Sage rankSolution Sage
    8 years ago

    heathernicole I am glad it worked for you, but I do have one other comment.  Typically I frown on hard coding 'Year' values in a measure like you do below :  " 'Calendar - Transaction Date'[Year] >= 2017 ".  Have you tried removing them from the measures to see if they make a difference? As written, the values shouldn't change.  (meaning the >= 2017 should be ignored anyway).   If you want to limit the graph to showing current year I typically use a slicer for that.  

     

    But, hey, if it works for you and you are happy with it then go for it.

     

     

    • heathernicole's avatar
      heathernicole
      Icon for Continued Contributor rankContinued Contributor
      8 years ago

      mattbrice - I completely agree with you. I normally do NOT hard code items like. In this sample dataset I was working with - I needed to filter out some bad data associated with 2016 and force the starting point to 2017. That's the ONLY reason I did that. 

       

      Thank you for making that point though - otherwise in a "clean" dataset - it's much better to leave that dynamic rather than hardcoding it. 

       

      Thanks! :)