Forum Discussion
TopN Customers Rolling 12 months
- 8 years ago
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 _Top20Last12MOand 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.
- 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.
- 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 _Top20Last12MOFinal Result:
Hi,
Please take a simple example and show the expected result (actual number that you want for the running total month on month).
- heathernicole8 years ago
Continued Contributor
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:
Total Top 20 # of Customers Who have ordered
(Rolling 12 months)2015 Dec $11,346,386 $6,816,041 60.07% 1780 2016 Jan $11,291,719 $6,780,296 60.05% 1812 2016 Feb $11,128,137 $6,600,841 59.32% 1874 2016 Mar $11,189,956 $6,473,342 57.85% 1970 2016 Apr $11,244,760 $6,531,868 58.09% 2011 2016 May $11,276,545 $6,590,446 58.44% 2091 2016 Jun $11,129,498 $6,424,677 57.73% 2091 2016 Jul $10,864,120 $6,228,246 57.33% 2117 2016 Aug $10,895,052 $6,201,174 56.92% 2102 2016 Sep $10,974,474 $6,271,319 57.14% 2155 2016 Oct $11,134,673 $6,317,821 56.74% 2176 2016 Nov $11,000,352 $6,232,941 56.66% 2200 2016 Dec $11,163,617 $6,572,118 58.87% 2225 2017 Jan $11,212,194 $6,362,380 56.75% 2230 2017 Feb $11,524,283 $6,567,634 56.99% 2320 2017 March $11,498,471 $6,525,786 56.75% 2345 2017 April $11,760,285 $6,418,982 54.58% 2392 2017 May $11,710,418 $6,584,612 56.23% 2396 2017 June $12,159,166 $6,608,196 54.35% 2417 2017 July $12,193,591 $6,480,111 53.14% 2420 2017 August $12,372,082 $6,511,825 52.63% 2408 2017 Sept $12,439,625 $6,548,643 52.64% 2408 2017 Oct $12,450,041 $6,494,657 52.17% 2411 2017 Nov $12,457,164 $6,448,778 51.77% 2365 2017 Dec $12,713,335 $6,472,475 50.91% 2322