hi,
I am trying to write a measure, but i am stuck!!
Basically i have a data set - below is an example:
| Item | Value | Date | Cost Withheld | Paid |
| 1.1 | 234.00 | 01/09/2018 | 0% | 234.00 |
| 1.1 | 345.00 | 01/10/2018 | 0% | 345.00 |
| 1.1 | 456.00 | 01/11/2018 | 20% | 364.80 |
| 1.1 | 234.00 | 01/12/2018 | 30% | 163.80 |
| 1.1 | 345.00 | 01/01/2019 | 0% | 345.00 |
| 1.2 | 269.10 | 01/09/2018 | 0% | 269.10 |
| 1.2 | 396.75 | 01/10/2018 | 0% | 396.75 |
| 1.2 | 524.40 | 01/11/2018 | 40% | 314.64 |
| 1.2 | 269.10 | 01/12/2018 | 0% | 269.10 |
| 1.2 | 396.75 | 01/01/2019 | 0% | 396.75 |
| 1.3 | 309.47 | 01/09/2018 | 0% | 309.47 |
| 1.3 | 456.26 | 01/10/2018 | 0% | 456.26 |
| 1.3 | 603.06 | 01/11/2018 | 20% | 482.45 |
| 1.3 | 309.47 | 01/12/2018 | 0% | 309.47 |
| 1.3 | 456.26 | 01/01/2019 | 0% | 456.26 |
I have a user relationship between this table (DATA) and another table (Withhold) - example:
| Date | 1.1 | 1.2 | 1.3 |
| 01/01/18 | 0% | 0% | 0% |
| 01/02/18 | 0% | 0% | 0% |
| 01/03/18 | 0% | 0% | 0% |
| 01/04/18 | 0% | 0% | 0% |
| 01/05/18 | 0% | 0% | 0% |
| 01/06/18 | 0% | 0% | 0% |
| 01/07/18 | 0% | 0% | 0% |
| 01/08/18 | 0% | 0% | 0% |
| 01/09/18 | 0% | 0% | 0% |
| 01/10/18 | 0% | 0% | 0% |
| 01/11/18 | 20% | 40% | 20% |
| 01/12/18 | 30% | 0% | 0% |
| 01/01/19 | 0% | 0% | 0% |
| 01/02/19 | 0% | 0% | 0% |
| 01/03/19 | 0% | 0% | 0% |
| 01/04/19 | 0% | 0% | 0% |
| 01/05/19 | 0% | 0% | 0% |
| 01/06/19 | 0% | 0% | 0% |
| 01/07/19 | 0% | 0% | 0% |
| 01/08/19 | 0% | 0% | 0% |
| 01/09/19 | 0% | 0% | 0% |
| 01/10/19 | 0% | 0% | 0% |
| 01/11/19 | 0% | 0% | 0% |
| 01/12/19 | 0% | 0% | 0% |
| 01/01/20 | 0% | 0% | 0% |
| 01/02/20 | 0% | 0% | 0% |
Basically I have a measure that extracts the percentage withheld, and puts the value in the corresponding line in the DATA file. And then i have a measure that works out how much is paid (less the % withheld).
NOW THE PROBLEM!
I have another data file (Paid Date):
| Item | Date Withheld | Date Paid | % Paid |
| 1.1 | 01/11/2018 | 01/12/2018 | 10% |
| 1.1 | 01/11/2018 | 01/01/2019 | 10% |
| 1.1 | 01/12/2018 | 01/01/2019 | 20% |
| 1.2 | 01/11/2018 | 01/01/2019 | 10% |
| 1.3 | 01/12/2018 | 01/01/2019 | 20% |
So for a particular item, elements of the % withheld are then 'released' on subsequent dates. Example is with Item 1.1 - you will see that orriginally on the 1/11/18 20% of the cost was withheld; 10% was then released on teh 1/12/18; and 10% was released on 1/1/19.
I am looking for a measure that would - for example give me the total paid on say the 1/1/19 for item 1.1 - this would be made up of the 345.00 that was paid on 1/1/19, PLUS the 10% of the value that was retained on the 1/11/18 and paid on the 1/1/19 (i.e. 10% of 456.00 = 45.60), PLUS the 20% of the value that was retained on the 1/12/18 and paid on the 1/1/19 (i.e. 20% of 234.00 = 46.80)
Does that make sense????
Welcome and views please.
Thanks
