Forum Discussion
Helper IIHelp with a calculated measure please
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
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] ) )
7 Replies
- LivioLanzo
Solution Sage
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.
timknoxLivioLanzo,
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' ?