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 

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

timknox's avatar
timknox
Helper II
7 years ago

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 

  • timknox's avatar
    timknox
    Helper II
    7 years ago

    is there a way i can upload a Power BI file to show you the issue?

     

    • timknox's avatar
      timknox
      Helper II
      7 years ago

      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:  

       

      ZIP File with data

       

      Welcome and help please

  • 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