Help with a calculated measure please

[timknox]

I will try and explain what i am rying to achieve!

I have a flat data file, about 25 columns x 800,000 rows.  I have reproduced a subset for the purpose of this question:

Code	Contract Item	Cost	Total Profit	Date
BAB	1.1	25.00	1.63	01/09/2018
BAB	1.1	35.00	2.28	01/09/2018
BAC	1.1	24.00	1.56	01/09/2018
BAD	1.2	28.00	1.83	01/09/2018
BAE	1.3	36.00	2.35	01/10/2018
BAB	1.1	29.00	1.89	01/10/2018
BAE	1.3	27.00	1.76	01/10/2018
BDD	4.5	58.00	3.78	01/09/2018
BDF	4.1	24.00	1.56	01/09/2018
BAB	1.1	22.00	1.43	01/10/2018

Basically i am trying to manipulate the 'Total Profit', basically for when it was actually paid.  The column 'Date' is when it should be paid, but in some circumstances they are not.

I have a related table ('Date' <> 'Date') which states whether the 'Total Profit' was paid:

Date	1.1	1.2	1.3	4.1	4.5
01/09/2018	0	1	0	0	0
01/10/2018	1	1	0	0	0
01/11/2018	1	1	1	1	1
01/12/2018	1	1	1	1	1
01/01/2019	1	1	1	1	1

A zero means it was not paid on that month, and a 1 that it was. So for the 'Total Profits' for Contract 1.1 that should have been paid on 01/09/2018 were not; the 'Total Profits' for Contract 1.2 that should have been paid on 01/09/2018 were.

I have another similarly related table  that states when it was finally paid, if there is a zero in the table above:

Date	1.1	1.2	1.3	4.1	4.5
01/09/2018	01/10/2018		01/10/2018	01/10/2018	01/10/2018
01/10/2018			01/11/2018	01/11/2018	01/11/2018
01/11/2018
01/12/2018
01/01/2019

So the 'Total Profits' for Contract 1.1 that should have been paid on 01/09/2018, were actually paid on 01/10/2018.

One should then end up with values like this:

Date	1.1	1.2	1.3	4.1	4.5
01/09/2018	0.00	1.83	0.00	0.00	0.00
01/10/2018	8.80	0.00	0.00	1.56	3.78
01/11/2018	0.00	0.00	4.11	0.00	0.00
01/12/2018	0.00	0.00	0.00	0.00	0.00
01/01/2019	0.00	0.00	0.00	0.00	0.00

And the Matrix i want to display in my report would look like this:

	01/09/2018	01/10/2019	01/11/2020	01/12/2021	01/01/2023
BAB		7.24
BAC		1.56
BAD	1.83
BAE			4.11
BDD		3.78
BDF		1.56

Can anyone help me please?

Many thanks

Tim

[LivioLanzo]

Hi @timknox

this is possible to do with some Power Query steps in order to prepare the data.

I have uploaded the PBIX here: https://1drv.ms/u/s!AiiWkkwHZChHjzpzpaIDO9ValBiQ

Let me know if something not clear.

[timknox]

LivioLanzo,

That is great - many thanks...... can you help me with one more step please!

I can produce a Matrix to show the 'Cost' against 'Date':

	01/09/2018	01/10/2019	01/11/2020	01/12/2021	01/01/2023
BAB	60.00	51.00
BAC	24.00
BAD	28.00
BAE		63.00
BDD	58.00
BDF	24.00

I now have the Matrix you developed for me (thank you) for when the 'Total Profit' was paid:

	01/09/2018	01/10/2019	01/11/2020	01/12/2021	01/01/2023
BAB		7.24
BAC		1.56
BAD	1.83
BAE			4.11
BDD		3.78
BDF		1.56

Challenge!!!  How do i get the 2 together to give me this:

	01/09/2018	01/10/2019	01/11/2020	01/12/2021	01/01/2023
BAB	60.00	58.24
BAC	24.00	1.56
BAD	29.83
BAE		63.00	4.11
BDD	58.00	3.78
BDF	24.00	1.56

Welcome your help again 

Many thanks

[LivioLanzo]

Hi @timknox,

thank you for the feedback.

Do you mean you want to show the cost by the 'Original Date' and the profit by the date I added: 'RealDate' ?

[timknox]

Yes - so it is pulling the two together into one Matrix

[LivioLanzo]

Hi @timknox

the quickest solution that comes to mind is to split the table I have created into two tables where one table would have the Cost and Original Date as Columns and the other table would have the Profit and 'RealDate' ( the date column I added ) as columns. 

These two tables would both be related to the calendar table. Then you would just need to create a measure that does

= SUM( CostTable[TotalCost] ) + SUM( ProfitTable[Profit] )

let me know if this works for you

[LivioLanzo · Accepted solution]

Another solution is to create an active relationship between the Calendar table and the 'Date' column and an inactive relationship between the Calendar Table and the 'RealDate' column I have added

then you can try something like this:

= SUM( Data[Cost] ) + CALCULATE( SUM(Data[Profit] ), USERELATIONSHIP( Data[RealDate], 'Calendar'[Date] ) )

[timknox]

Bingo - very many thanks for your help