Forum Discussion
Help with a 'complicated' measure
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
11 Replies
hi, timknox
I'm a little confused about your description, 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 couldn't find it in the sample data table.
and what is the expected output based on these data.
Best Regards,
Lin
hi Lin,
Thanks for your help........
So the released amounts and dates are in the bottom table (Paid Date). So what we need to do is to match the 'Item No' and the 'Date Withheld', to obtain the 'Date Paid' and the '% Paid'
Then with this data one needs to go back to the main data set and look up what the 'Value' was for this Item - in this case it will be 456.00
Then we need to work out for the 1/12/18 that 10% was paid - i.e. 45.60 and this figure needs to show up in the 1/12/18 column.
Does that make sense?
Kind regards
Tim
is there a way i can upload a Power BI file to show you the issue?
