Monthly Account Changes

[maek86]

Each month I have a list of accounts that changes. The sales performance for each month must stay with that month's list of accounts. What is the best way to manage this process in power bi? Currently, all my sales are only based off of the current month's list of accounts. While that list of accounts can change month to month.

Contract X = Accounts(A,B,C,D,E) when signed. As time progresses the contract cosists of different accounts.

Example:

May Sales = Accounts(A,B,C,D,E)

Jun Sales = Accounts (A,B,C)

July Sales = Accounts (A,B,C,D)

and so on. 

What are your thoughts on this?

[Anonymous · Accepted solution]

I've got a fairly complex answer that you might be able to work from.  I recently posted a solution to a problem here:

https://community.powerbi.com/t5/Desktop/Get-distinct-Employee/m-p/192034#M84566

In your case, you could modify that to create a table of Accounts by parsing that list of Account names.  Rather then using the count method, you could instead use those to form a SUMX method that iterates over each of the distinct values. 

Something like this?  (You just need that dummy table i've mentioned, with rows going to the max accounts you could have in a single month)

SalesRevenue = SUMX(			//This is the row that does the sum of each account
	SUMMARIZE(					//This will make the distinct values in our column
		ADDCOLUMNS(				//This creates the calculated column of our Account Names
			FILTER(				//This cuts down the dummy table to only be the size of the number of Names we have
				CROSSJOIN(		//This Merges our Dummy Table with the Account Names
					SUMMARIZE(	//This creates each 'Account Name' row
						MonthlyTable,
						MonthlyTable[AccountsList],
						MonthlyTable[SalesMonth],
						"NamesCnt",
						1 + len(MonthlyTable[AccountsList]) - len(SUBSTITUTE(MonthlyTable[AccountsList], "/", ""))	//Count of Slashes
					),
					DummyTbl
				),
				DummyTbl[Dummy] <= [NamesCnt]
			),
			"SubName",
			PATHITEM(			// This function splits up the Employee names to be placed in each row
				SUBSTITUTE(MonthlyTable[AccountsList], ",", "|"),
				DummyTbl[Dummy]
			)		
		),
		[SubName]
	),
	[SubName],
	'SalesTable'[Revenue]
)

Assumption:  That your accountslist field will be holding the data like "A,B,C" rather than "Accounts(A,B,C"