Return earliest date of achieving specific target amount

[Jamie_wong]

I have 3 Tables.

Table 1: WIP - Batch List of each project

Project ID, Batch Name

1, 1A

1, 1B

1, 1C

1, 1D

2, 2A

2, 2B

2, 2C

Table 2: Delivery - If Output Date is not same as Delivery Date, there will have another record varied by General Delivery Date

Project ID, Batch Name, Output Date, Delivery Date, General Delivery Date

1, 1A, 2020-03-27, 2020-03-29, 2020-03-27  --- General Delivery Date refer to Output Date

1, 1A, 2020-03-27, 2020-03-29, 2020-03-29  --- General Delivery Date refer to Delivery Date

1, 1B, 2020-03-29, 2020-04-01, 2020-03-29  --- General Delivery Date refer to Output Date

1, 1B, 2020-03-29, 2020-04-01, 2020-04-01  --- General Delivery Date refer to Delivery Date

1, 1C, 2020-04-02, 2020-04-02, 2020-04-02  --- General Delivery Date refer to Output Date/Delivery Date

1, 1D, 2020-04-01,   , 2020-04-01  --- General Delivery Date refer to Output Date

2, 2A, 2020-03-27, 2020-03-29, 2020-03-27  --- General Delivery Date refer to Output Date

2, 2A, 2020-03-27, 2020-03-29, 2020-03-29  --- General Delivery Date refer to Delivery Date

2, 2B, 2020-03-28,   , 2020-03-28  --- General Delivery Date refer to Output Date

2, 2C, 2020-03-29,   , 2020-03-29  --- General Delivery Date refer to Output Date

Table 3: SOW - If Start Date is not same as Target date of delivery 50% Batch, there will have another record varied by General SOW Date

Project ID, Start Date, Target date of delivery 50% Batch, General SOW Date

1, 2020-03-27, 2020-04-02, 2020-03-27  --- General SOW Date refer to Start Date

1, 2020-03-27, 2020-04-02, 2020-04-02  --- General SOW Date refer to Target date of delivery 50% Batch

2, 2020-03-26, 2020-03-28, 2020-03-26  --- General SOW Date refer to Start Date

2, 2020-03-26, 2020-03-28, 2020-03-28  --- General SOW Date refer to Target date of delivery 50% Batch

Result Table: 

Project ID, Batch Count, Target 50% Batch, Target date of delivery 50% Batch, Hit 50% Date, Total Batch Hit 50% Date

1, 4, 2, 2020-04-02, 2020-04-01, 3

2, 3, 1, 2020-04-02, 2020-03-29, 1

** Batch Count refer to Table 1 by counting number of batch from each project.

I want to know how to get Hit 50% Date (refer to when achieved 50% batch delivery) and Total Batch Hit 50% Date (refer to how many batch delivered as of Target date of delivery 50% Batch)

Hope anyone can help on this. Thanks!

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Not quite following this.

[Jamie_wong]

Hi @Greg_Deckler , 

Here are the tables:

Table 1:

Table 1

Table 2: 

Table 2

Table 3:

Table 3

Result Table:

Result Table

** Batch Count - Total Batch of the project. 
    For Example: In Table 1, Project 1 has Batch 1A, 1B, 1C, 1D, total 4 Batches. (can refer to Table 1)

** Target 50% Batch - 50% of the Batch Count

    For Example: Project 1 has total of 4 batches, 50% of it is 2 Batches.

** Target date of delivery 50% Batch - Target date to deliver 50% batches.

    For Example: On 2020-04-02, we should deliver at least 2 batches from Project 1. (can refer to Table 3)

** Hit 50% Date - Earliest date of delivery total of 50% batches of the project.

    For Example: On 2020-04-01, we made to deliver total of 2 batches for Project 1 (can refer to Table 2)

** Total Batch Hit 50% Date -  On Target date of delivery 50% Batch (Column 4), Accumulated total of batches delivered.

    For Example: For Project 1, we has delivered total of 3 batches from starting to 2020-04-02.

Can you help on measuring the Hit 50% Date and Total Batch Hit 50% Date? 

[Greg_Deckler]

Right, I'm not typing all that data in. Please post as text in a table. There is a link to an excellent article written by @ImkeF on how to properly post data to the forums. It is in the "How to get your question answered quickly link" I posted earlier.