Forum Discussion

OUWL's avatar
OUWL
Frequent Visitor
6 years ago
Solved

Finding calculation between two dates

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-XX...
Need Help
  • dax's avatar
    dax
    6 years ago

    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.

     

OUWL's avatar
OUWL
Frequent Visitor
6 years ago

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's avatar
    dax
    Community Support
    6 years ago

    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's avatar
      OUWL
      Frequent Visitor
      6 years ago

      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 CountMM Complete DateMaterial Master "Working Days"
      293/23/20182
      697/10/20189
      297/12/201810
      237/13/20185
      507/16/20184
      988/16/20184
      898/16/20184
      708/16/20185
      968/16/20184
      1038/16/20184
      6912/5/201832
      10112/5/201833
      9912/5/201833
      823/12/201914
      503/8/201913
      210/22/20185
      4010/18/20181
      103/18/20196
      173/14/20194
      234/9/201912
      154/9/20198
      315/21/20191
      519/18/201916
      549/18/201916
      12010/2/201915
      452/25/202014
      92/10/202015
      113/5/20202
      46  

      Regards,

      Reece

       

      • OUWL's avatar
        OUWL
        Frequent Visitor
        6 years ago

        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.