cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Continued Contributor
Continued Contributor

TopN Customers Rolling 12 months

Ok - So here's what I'm trying to recreate in Power BI (currently an excel file):

Top 20 and Count of Customers - Rolling 12 PBI.JPG 

 

Logically, I get what's occurring in this report - however it's escaping me as to HOW to actually pull this off. 

 

Count of Customers I'm not worried about - I can do that one. 

 

But the Rolling 12 months sales showing ONLY the TOP 20 Customers (Highest sales) - which fluctuates from month to month - I'm hitting a wall. 

I have:

  • Sales Details table that contains outgoing sales (Sales$$)
  • Customer Table (customer name)
  • Calendar Table (Transaction Date)

 

They want me to recreate the report in the image - just automating the process and have the rolling 12 months move on it's own. 

 

ANY ideas in the right direction would be extremely helpful. 

 

Edit:

This is what I have so far:

 

Sales $ RunTot in Month = 
CALCULATE(
	SUM('SALES DETAILS'[Sales $$]),
	FILTER(
		CALCULATETABLE(
			SUMMARIZE(
				'Calendar - Transaction Date',
				'Calendar - Transaction Date'[Transaction Date Month Number],
                'Calendar - Transaction Date'[Year],
                'Calendar - Transaction Date'[Month]
			),
			ALLSELECTED('Calendar - Transaction Date')
		),
		ISONORAFTER(
			'Calendar - Transaction Date'[Transaction Date Month Number], MAX('Calendar - Transaction Date'[Transaction Date Month Number]), DESC,
			'Calendar - Transaction Date'[Month], MAX('Calendar - Transaction Date'[Month]), DESC
		)
	)
)

Which gets me closer but it's restarting at the start of the new year - not quite what I want.

 

RunTot.JPG

~heathernicoale
3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

This is an interesting problem so I gave it a try.  Let me know if it doesn't work for you as I haven't tested these as I don't really have a good sample data set to work off of.  You'll have to translate the column names I used for your model.

Here is what I came up with for 4 measures assuming you have YearMonth on rows like you have in your result screenshots.

 

Total Sales = SUM ( FactSales[SalesAmount] )

 Then for total sales last 12 Mo

 

Total Sales Last 12MO =
VAR _last12MO =
    DATESBETWEEN (
        DimDate[Datekey],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( DimDate[Datekey] ) ) ),
        LASTDATE ( DimDate[Datekey] )
    )
RETURN
    CALCULATE ( [Total Sales], _last12MO )

then for rolling top 20 sales last 12 months:

 

Total Sales Last 12 MO Rolling Top20 =
VAR _last12MO =
    DATESBETWEEN (
        DimDate[Datekey],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( DimDate[Datekey] ) ) ),
        LASTDATE ( DimDate[Datekey] )
    )
VAR _Top20Last12MO =
    CALCULATE (
        SUMX (
            VALUES ( DimDate[YearMonth] ),
            CALCULATE (
                [Total Sales],
                TOPN ( 20, VALUES ( DimCustomer[CustomerKey] ), [Total Sales] )
            )
        ),
        _last12MO
    )
RETURN
    _Top20Last12MO

and finally the percentage is easy:

 

Percentage of Sales = DIVIDE ( [Total Sales Last 12 MO Rolling Top20], [Total Sales Last 12MO] )

Let me know if this worked.

View solution in original post

Highlighted

@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. 

 

~heathernicoale

View solution in original post

Highlighted

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:

Final Result.JPG

 

~heathernicoale

View solution in original post

8 REPLIES 8
Highlighted
Super User III
Super User III

Hi,

 

Please take a simple example and show the expected result (actual number that you want for the running total month on month).


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Solution Sage
Solution Sage

This is an interesting problem so I gave it a try.  Let me know if it doesn't work for you as I haven't tested these as I don't really have a good sample data set to work off of.  You'll have to translate the column names I used for your model.

Here is what I came up with for 4 measures assuming you have YearMonth on rows like you have in your result screenshots.

 

Total Sales = SUM ( FactSales[SalesAmount] )

 Then for total sales last 12 Mo

 

Total Sales Last 12MO =
VAR _last12MO =
    DATESBETWEEN (
        DimDate[Datekey],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( DimDate[Datekey] ) ) ),
        LASTDATE ( DimDate[Datekey] )
    )
RETURN
    CALCULATE ( [Total Sales], _last12MO )

then for rolling top 20 sales last 12 months:

 

Total Sales Last 12 MO Rolling Top20 =
VAR _last12MO =
    DATESBETWEEN (
        DimDate[Datekey],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( DimDate[Datekey] ) ) ),
        LASTDATE ( DimDate[Datekey] )
    )
VAR _Top20Last12MO =
    CALCULATE (
        SUMX (
            VALUES ( DimDate[YearMonth] ),
            CALCULATE (
                [Total Sales],
                TOPN ( 20, VALUES ( DimCustomer[CustomerKey] ), [Total Sales] )
            )
        ),
        _last12MO
    )
RETURN
    _Top20Last12MO

and finally the percentage is easy:

 

Percentage of Sales = DIVIDE ( [Total Sales Last 12 MO Rolling Top20], [Total Sales Last 12MO] )

Let me know if this worked.

View solution in original post

Highlighted

Hello @Ashish_Mathur - 

 

the image attached IS the rolling number I need to come up with. That report in the image - part of it is run out of QuickBooks - extracted - then manually worked on in Excel by the Sales Manager to get the final result. 

 

I have access to the database on the backend - so what I'm working with to try and automate this report is quite different.

 

 

Here's a sample set that can be copied though that gives the rolling totals I need to recreate for last year in my report:

 

  TotalTop 20  # of Customers Who have ordered
(Rolling 12 months)
2015Dec$11,346,386$6,816,04160.07% 1780
2016Jan$11,291,719$6,780,29660.05% 1812
2016Feb$11,128,137$6,600,84159.32% 1874
2016Mar$11,189,956$6,473,34257.85% 1970
2016Apr$11,244,760$6,531,86858.09% 2011
2016May$11,276,545$6,590,44658.44% 2091
2016Jun$11,129,498$6,424,67757.73% 2091
2016Jul$10,864,120$6,228,24657.33% 2117
2016Aug$10,895,052$6,201,17456.92% 2102
2016Sep$10,974,474$6,271,31957.14% 2155
2016Oct$11,134,673$6,317,82156.74% 2176
2016Nov$11,000,352$6,232,94156.66% 2200
2016Dec$11,163,617$6,572,11858.87% 2225
2017Jan$11,212,194$6,362,38056.75% 2230
2017Feb$11,524,283$6,567,63456.99% 2320
2017March$11,498,471$6,525,78656.75% 2345
2017April$11,760,285$6,418,98254.58% 2392
2017May$11,710,418$6,584,61256.23% 2396
2017June$12,159,166$6,608,19654.35% 2417
2017July$12,193,591$6,480,11153.14% 2420
2017August$12,372,082$6,511,82552.63% 2408
2017Sept$12,439,625$6,548,64352.64% 2408
2017Oct$12,450,041$6,494,65752.17% 2411
2017Nov$12,457,164$6,448,77851.77% 2365
2017Dec$12,713,335$6,472,47550.91% 2322
~heathernicoale
Highlighted

@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. 

 

~heathernicoale

View solution in original post

Highlighted

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:

Final Result.JPG

 

~heathernicoale

View solution in original post

Highlighted
Continued Contributor
Continued Contributor

ADDED BONUS - the correct answer is below 🙂 But I added Dynamic TOPN filtering and thought it might be helpful to someone else. 🙂  I modifed the method used here: by the PowerPivotPro guys (Rob Collie wrote this particular article) just a touch to work in the Power BI model. Great article and there's a part 1 and part 3 included with it if you're interested. Reid Havens referenced all three of them in a blog post here

 

To make this report filter dynamically by the TOPN:

 

First I created a Top N table (same as in the article):

You do this just by 'Enter Data' and manually create the table obviously you can go up as high as you want - for this report it was 20.

 

Enter Data.JPG

TopNTable.JPG 

 

The created the following measure: 

SelectedTopNNumber = MAX('TOPN'[TopN]) 

 

Then I modified @mattbrice measure to reference the newly created TopN measure: It's bold and underlined. This way the TOPN is dynamic according to whichever TOPN number is selected. 

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 ( [SelectedTopNNumber], VALUES ( 'SALES DETAILS'[LinkToCustomerID] ), [Total Outgoing Sales] )
            )
        ),
        _last12MO
    )
RETURN
    _Top20Last12MO

 

I then created a chiclet slicer (imported it from the Visual Store). Don't forget to disable multi-select within the filter. 

 TopNFilter.JPG 

 

Just like the article - it gives you the ability to dynamically filter this sample dataset by a TopN filter. 

Filtered by Top 20:

Filtered by Top20.JPG

 

Filtered by Top 5

Filtered by Top5.JPG

 

 

 

 

~heathernicoale
Highlighted

@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.

 

 

Highlighted

@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! 🙂 

~heathernicoale

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors