Help with a measure please

[timknox]

I was wondering if someone could help me with a measure please.

I have a very large data file (25 columns x 600,000 rows) which contains a vast amount of data, but eventually i have managed to produce a Matrix in a report that looks like:

Code	01/04/2018	01/05/2018	01/06/2018	01/07/2018	01/08/2018	01/09/2018	01/10/2018	01/11/2018	01/12/2018	01/01/2019	01/02/2019	01/03/2019
BACB	245	306	383	479	598	748	935	1,168	1,460	1,825	2,282	2,852
BACD	221	276	345	431	538	673	841	1,051	1,314	1,643	2,054	2,567
BACC	466	582	727	909	1,136	1,421	1,776	2,220	2,775	3,468	4,335	5,419
BACE	419	524	655	818	1,023	1,279	1,598	1,998	2,497	3,121	3,902	4,877
BAGG	884	1,106	1,382	1,727	2,159	2,699	3,374	4,217	5,272	6,590	8,237	10,296
BABG	429	536	670	837	1,046	1,308	1,635	2,044	2,555	3,193	3,992	4,989
TOTAL	2,663	3,329	4,161	5,201	6,501	8,127	10,158	12,698	15,873	19,841	24,801	31,001

In the report there are more Codes, but i have reduced it for simplicity.

The table above comes from 2 x measures:

The values in RED are payments that have been Invoiced and Paid to date.

The values in BLACK are forecasted payments to be made in the future months.

I have a measure that basically says, if the Date >= 1/11/18 use the forecasted values, otherwise use the invoiced and paid data.

That all works. 🙂

MY ISSUE

Some invoices received are not paid straight away, but i know we will be paying them at some time in the future.  For example we may withold these payments in the respective months:

Code

01/04/2018

01/05/2018

01/06/2018

01/07/2018

01/08/2018

01/09/2018

01/10/2018

01/11/2018

01/12/2018

01/01/2019

01/02/2019

01/03/2019

BACB

45

58

BACD

225

152

I have a Measure that works out what has not been paid, and which Code.

What i need help with is as follows.

I need to take the total for each Code, and spread it out across the remaining months:

Code	01/04/2018	01/05/2018	01/06/2018	01/07/2018	01/08/2018	01/09/2018	01/10/2018	01/11/2018	01/12/2018	01/01/2019	01/02/2019	01/03/2019
BACB								20.6	20.6	20.6	20.6	20.6
BACD								75.4	75.4	75.4	75.4	75.4

And then i need to add these values into the Measure i have to obtain the data on teh right of my table, so i end up with:

Code	01/04/2018	01/05/2018	01/06/2018	01/07/2018	01/08/2018	01/09/2018	01/10/2018	01/11/2018	01/12/2018	01/01/2019	01/02/2019	01/03/2019
BACB	245	306	383	479	598	748	935	1,189	1,481	1,846	2,302	2,873
BACD	221	276	345	431	538	673	841	1,127	1,390	1,718	2,129	2,642
BACC	466	582	727	909	1,136	1,421	1,776	2,220	2,775	3,468	4,335	5,419
BACE	419	524	655	818	1,023	1,279	1,598	1,998	2,497	3,121	3,902	4,877
BAGG	884	1,106	1,382	1,727	2,159	2,699	3,374	4,217	5,272	6,590	8,237	10,296
BABG	429	536	670	837	1,046	1,308	1,635	2,044	2,555	3,193	3,992	4,989
TOTAL	2,663	3,329	4,161	5,201	6,501	8,127	10,158	12,794	15,969	19,937	24,897	31,097

Welcome any help.

Many thanks

[Greg_Deckler]

OK, there is a lot here but not sample source data so some of this is guessing. But, if you have a measure that calculates what has not been paid, then you could write a measure like this:

Measure = 
VAR __code = MAX([Code]) //current code in matrix
VAR __table = SUMMARIZE(ALL('Table'),[Code],[Month],"__2BPaid",[Measure to be Paid]) //summarize your data table by code, month (date) and your measure calculation of what remains to be paid
RETURN
SUMX(FILTER(__table,[Code] = __code),[__2BPaid]) //filter your table down to your code and sum up the 2BPaid column giving you the total to be paid

That should give you the total to be paid. I can only surmise that you are able to get a count of the remaining months somehow so then you just divide by that to get a number for each future month and then you should be good.

[timknox]

I have tried this - thanks

I am getting an error message:  "Resources Exceeded - this visual has exceeded the available rrsources"

Any ideas?

[timknox]

I have the figure which I need to spread out as a separate table. Code and Value

Problem is when I try and distribute it I end up with the original table with amounts per month and not I. The Forecast months!

Somehow I need to reference the total and Code, and then add that vale to each total for the Code in the Forecast months only!

Any ideas how I could achieve that?