Forum Discussion
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...
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
- =IFERROR((SUMIFS(table1[Old PN Count],Table1[MM Complete Date]">"
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