cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jamie_wong
Frequent Visitor

Return earliest date of achieving specific target amount

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!

 

 

 

3 REPLIES 3
Greg_Deckler
Super User IV
Super User IV

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Greg_Deckler , 

Here are the tables:

Table 1:

Table 1Table 1

 

Table 2: 

Table 2Table 2

 

Table 3:

Table 3Table 3

 

Result Table:

Result TableResult 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? 

 

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors