The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
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
hi, @timknox
It seems that the link is the loss, I'm sorry what I am confused about is that in the bottom table (Paid Date) for Item 1.1, there is only 3 rows of data and how to understand "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."
For your case, I think it is a "merge" or "lookup" case.
You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
HI, @timknox
This link works, and the same point confused.
EXAMPLE FOR CONTRACT ITEM 1.1 - 20% that was withheld on 1/11/18; 10% was paid on 1/12/18 and 10% was paid on 1/1/19
EXAMPLE FOR CONTRACT ITEM 1.1 - 30% that was withheld on 1/12/18; 30% was paid on 1/1/19
EXAMPLE FOR CONTRACT ITEM 1.2 - 20% that was withheld on 1/10/18; 20% was paid on 1/12/18
For example
Do you mean it is based on the Percentage Cost Withold (Paid) table,
But why 1.3 has no data, and for 1.1 TOTAL Paid in 1/1/2019 is should 222+23.3+136.8-0=382.1
and 1.2 TOTAL Paid in 1/1/2019 is should 273.95, why it needs to add 136.80?
Best Regards,
Lin
But why 1.3 has no data, >>>>> i just did not do the example 🙂
and for 1.1 TOTAL Paid in 1/1/2019 is should 222+23.3+136.8-0=382.1 >>>>>> Correct - sorry
and 1.2 TOTAL Paid in 1/1/2019 is should 273.95, why it needs to add 136.80? >>>>>> Correct - sorry
i have updated the file [Output Required]
https://www.dropbox.com/s/8rizb42cqku62mh/OutPut%20Required.xlsx?dl=0
I have updated the tblFOO file, as there will be more than one item under a contract item.....
For example, Contract Item 1.1 has the following different items under it:
Deliver box |
Deliver box scews |
Deliver square box |
Deliver small box |
The link for the revised file is here:
hi Lin,
I am sorry i am not explaining it clearly, but thank you for your help.
So the underlying cost data is contained in the Excel file tblFOO. This show in the [col Total Cost] what should be paid for each [Contract Item] on the [Date].
However, due to contract reasons, a % is sometimes withheld until some work is completed.
This is measured as a % of the original [col Total Cost].
This is demonstrated in the sheet [Percentage Cost Withhold]. NOTE: we do not withhold for every payment or contract item.
So as an example, with Contract Item 1.1, on the 1st Nov 2018 we have decided to withhold 20% of the [col Total Cost] until work is completed. This value is 20% of 233.00 (46.60). So on the 1/11/18 we actually pay only 186.400 (233.00 - 46.60).
Then, using [Percentage Cost Withold (Paid)] you will see that on the 1st Dec 2018 they had completed some of the work, and they were claiming half of the 20% withheld (shown as 10% in the table).
So on 1/12/2018 we will then pay half of the value that was withheld in November - i.e. we pay 23.30 on the 1/12/18.
Similarly in January they claim the balance, so we pay the final 23.30 in January 2019.
Does that help?
is there a way i can upload a Power BI file to show you the issue?
I have now managed to put together a sample file, with the supporting Excel data files.
I have included a further Excel file showing the required output.
The files can be downloaded here:
Welcome and help please
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
80 | |
74 | |
52 | |
50 |
User | Count |
---|---|
132 | |
124 | |
78 | |
64 | |
61 |