Finding calculation between two dates

[OUWL]

I have Two rows that have One Assignee and a One Reviewer, 

Assignee - Create Date & Complete date

Reviewer - Create Date & Complete date

ECN	CT	Name	Create	Complete	Role
CN-XXXX1	XXXXX	ID Parts	1/01/2018	1/03/2018	Assignee
CN-XXXX1	XXXXX	ID Parts	1/03/2018	1/10/2018	Reviewer

I would like to calculate/sum between "Assignee" Create Date and "Reviewer" Complete Date

[Greg_Deckler]

@OUWL - Are you looking for this in a measure or a column. Should the value show up for both or just for one? Trying to understand the use case but generally you are going to need EARLIER. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[OUWL]

my task is to find the avg. # of Parts Processed Per/Week  using 12 months / 3 months

using a measure.  the issue I am having is the Material Master (Excel)  is calculated from Assignee start date to Reviewer Complete date so I only need that calculation seen below.

=IFERROR((SUMIFS(Table1[Old PN Count],Table1[MM Complete Date],">"
&TODAY()-365)/SUMIFS(Table1[Material Master],Table1[MM Complete Date],">"
&TODAY()-365))*5,"-")

=IFERROR((SUMIFS(Table1[Old PN Count],Table1[MM Complete Date],">"
&TODAY()-90)/SUMIFS(Table1[Material Master],Table1[MM Complete Date],">"
&TODAY()-90))*5,"-")

Regards

[dax]

Hi @OUWL , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[OUWL]

Hi @dax my task is to convert an Excel status report to Power Bi report,  "Part Consolidation Status Report"

•  Source list of Old Material numbers to be consolidated - typically 50 parts per list or PLM Change Request
  • PLM we are using has tasks for engineering, business groups, Logistics
  • Using Old Part number count & using the task completion dates
• example ("This Formula is for ERP Material Master (Business Group Task)
  • =IFERROR((SUMIFS(table1[Old PN Count],Table1[MM Complete Date]">"
    &TODAY()-365/SUMIFS(Table1[Material Master],Table1[MM Complete Date],">"&TODAY()-365))*5

Criteria:

Old PN Count	MM Complete Date	Material Master "Working Days"
29	3/23/2018	2
69	7/10/2018	9
29	7/12/2018	10
23	7/13/2018	5
50	7/16/2018	4
98	8/16/2018	4
89	8/16/2018	4
70	8/16/2018	5
96	8/16/2018	4
103	8/16/2018	4
69	12/5/2018	32
101	12/5/2018	33
99	12/5/2018	33
82	3/12/2019	14
50	3/8/2019	13
2	10/22/2018	5
40	10/18/2018	1
10	3/18/2019	6
17	3/14/2019	4
23	4/9/2019	12
15	4/9/2019	8
31	5/21/2019	1
51	9/18/2019	16
54	9/18/2019	16
120	10/2/2019	15
45	2/25/2020	14
9	2/10/2020	15
11	3/5/2020	2
46

Regards,

Reece

[OUWL]

@dax, sorry the output should be in this example 16.3 average for 12 months and 5.4 average for 3 months.  find the amount of parts process per week.

[dax]

Hi @OUWL , 

If possible, could you please explain how to get the result of "16.3 average for 12 months and 5.4 average for 3 months". I am not familiar with your Excel expression, could you please write the expression of above result(such as (11+22)/(22+33))?

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[OUWL]

@dax

Sumifs:

Old Part Count,MM Complete Date > today-365: 294

/

Material Master,MM Complete Date > today() -365: 90*5

(294/90)*5

Regards,

Reece

[dax · Accepted solution]

Hi @OUWL , 

You could refer to my sample to see wheter it is what you want, if this is not waht you want, please correct me.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.