Forum Discussion

timknox's avatar
timknox
Helper II
7 years ago

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%     ...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

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

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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


     

    • timknox's avatar
      timknox
      Helper II
      7 years ago

      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?

    • timknox's avatar
      timknox
      Helper II
      7 years ago

      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