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

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.

  • dax's avatar
    dax
    Community Support
    6 years ago

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

      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's avatar
        dax
        Community Support
        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.