Hi,
I need guidance with the following requirements in Power Query & Power Pivot.
I have reports that are generated daily. The reports are then appended to calculate to perform a set of calculations;
The pivot chart would facilitate presenging ageing trend.
I need guidance with the following requirements in Power Query & Power Pivot.
I have reports that are generated daily. The reports are then appended to calculate to perform a set of calculations;
- Ageing
- Age Group
- % of backlog by age group by extracted date
- % running total by extracted date
| Report Date | ReceiveDate | CustomerReqDate | SalesOrderNo | ProductNo | OrderQty | ConfirmedQty | UnconfirmedQty | CompleteDelivery | Ageing | AgeGroup | |
| 01/22/24 | 06/22/2023 | 09/15/2023 | 112935380 | 1NR73A-K1H | 1 | 0 | 1 | N | 35 | [31 - 60] | |
| 01/22/24 | 07/03/2023 | 07/07/2023 | 113430548 | A1A30A-KGJ | 1 | 0 | 1 | N | 26 | [21 - 30] | |
| 01/22/24 | 07/04/2023 | 07/08/2023 | 113493710 | 1NR73A-K1H | 1 | 1 | 0 | N | 25 | [21 - 30] | |
| 01/22/24 | 07/05/2023 | 07/09/2023 | 113512547 | A1A30A-KGJ | 1 | 0 | 1 | N | 24 | [21 - 30] | |
| 01/22/24 | 07/06/2023 | 07/10/2023 | 113543813 | 5RA32F-KGJ | 1 | 0 | 1 | N | 23 | [21 - 30] | |
| 01/22/24 | 07/07/2023 | 07/11/2023 | 113561139 | A1A30A-KGJ | 1 | 0 | 1 | N | 22 | [21 - 30] | |
| 01/22/24 | 07/08/2023 | 07/12/2023 | 113705782 | A1A77A-KGJ | 10 | 5 | 5 | N | 21 | [21 - 30] | |
| 01/22/24 | 07/09/2023 | 07/13/2023 | 113725103 | N7L37A | 1 | 1 | 0 | N | 21 | [21 - 30] | |
| 01/22/24 | 07/10/2023 | 07/14/2023 | 113900946 | 2Z600F-KGJ | 1 | 0 | 1 | N | 20 | [11 - 20] | |
| 01/22/24 | 07/11/2023 | 07/15/2023 | 113933893 | 3JA05AN-150 | 1 | 0 | 1 | N | 19 | [11 - 20] | |
| 01/22/24 | 07/12/2023 | 07/16/2023 | 113969730 | A1A56A-KGJ | 1 | 0 | 1 | N | 18 | [11 - 20] | |
| 01/22/24 | 07/13/2023 | 07/17/2023 | 113987750 | 5RA32F-KGJ | 1 | 1 | 0 | N | 17 | [11 - 20] | |
| 01/22/24 | 07/14/2023 | 07/18/2023 | 114002150 | 2Z613F-KGJ | 2 | 1 | 1 | N | 16 | [11 - 20] | |
| 01/22/24 | 07/15/2023 | 07/19/2023 | 114002150 | 5RA32F-KGJ | 1 | 1 | 0 | N | 15 | [11 - 20] | |
| 01/22/24 | 07/16/2023 | 07/20/2023 | 114003452 | 2Z600F-KGJ | 5 | 2 | 3 | N | 15 | [11 - 20] | |
| 01/22/24 | 07/17/2023 | 07/21/2023 | 114005180 | 2Z600F-KGJ | 1 | 1 | 0 | N | 14 | [11 - 20] | |
| 01/22/24 | 07/18/2023 | 07/22/2023 | 114016353 | A1A77A-KGJ | 1 | 0 | 1 | N | 13 | [11 - 20] | |
| 01/22/24 | 07/19/2023 | 07/23/2023 | 110113433 | 3GZ25A-KGJ | 1 | 0 | 1 | Y | 12 | [11 - 20] | |
| 01/22/24 | 07/20/2023 | 07/24/2023 | 110113433 | K5L35A | 1 | 1 | 0 | Y | 11 | [11 - 20] | |
| 01/22/24 | 07/21/2023 | 07/25/2023 | 110113433 | 3GZ25A-KGJ | 1 | 0 | 1 | Y | 10 | [6 - 10] | |
| 01/22/24 | 07/22/2023 | 07/26/2023 | 110113433 | K5L35A | 1 | 1 | 0 | Y | 9 | [6 - 10] | |
| 01/22/24 | 07/23/2023 | 07/27/2023 | 110113433 | Y3D03A | 1 | 1 | 0 | Y | 9 | [6 - 10] | |
| 01/22/24 | 07/24/2023 | 07/28/2023 | 110113433 | Z7C07A | 1 | 1 | 0 | Y | 8 | [6 - 10] | |
| 01/22/24 | 07/25/2023 | 07/29/2023 | 110113433 | 3PZ35A-KGJ | 1 | 1 | 0 | Y | 7 | [6 - 10] | |
| 01/22/24 | 07/26/2023 | 07/30/2023 | 110113433 | D7P27A | 1 | 1 | 0 | Y | 6 | [6 - 10] | |
| 01/22/24 | 07/27/2023 | 07/31/2023 | 110113433 | 3PZ35A-KGJ | 1 | 1 | 0 | Y | 5 | [0 - 5] | |
| 01/22/24 | 07/28/2023 | 08/01/2023 | 110113433 | D7P27A | 1 | 0 | 1 | Y | 4 | [0 - 5] | |
| 01/22/24 | 07/29/2023 | 08/02/2023 | 110433907 | 3GZ25A-KGJ | 1 | 0 | 1 | Y | 3 | [0 - 5] | |
| 01/22/24 | 07/30/2023 | 08/03/2023 | 110433907 | K5L35A | 1 | 1 | 0 | Y | 3 | [0 - 5] | |
| 01/22/24 | 07/31/2023 | 08/04/2023 | 110433907 | Y3D03A | 1 | 1 | 0 | Y | 2 | [0 - 5] | |
| 01/22/24 | 08/01/2023 | 08/05/2023 | 110433907 | Z7C07A | 1 | 1 | 0 | Y | 1 | [0 - 5] | |
| 01/22/24 | 08/02/2023 | 08/06/2023 | 110433907 | 3GZ25A-KGJ | 1 | 0 | 1 | Y | 0 | [0 - 5] | |
| 01/23/24 | 6/22/2023 | 9/15/2023 | 112935380 | 1NR73A-K1H | 1 | 0 | 1 | N | 35 | [31 - 60] | |
| 01/23/24 | 7/3/2023 | 7/7/2023 | 113430548 | A1A30A-KGJ | 1 | 0 | 1 | N | 26 | [21 - 30] | |
| 01/23/24 | 7/4/2023 | 7/8/2023 | 113493710 | 1NR73A-K1H | 1 | 1 | 0 | N | 25 | [21 - 30] | |
| 01/23/24 | 7/5/2023 | 7/9/2023 | 113512547 | A1A30A-KGJ | 1 | 0 | 1 | N | 24 | [21 - 30] | |
| 01/23/24 | 7/6/2023 | 7/10/2023 | 113543813 | 5RA32F-KGJ | 1 | 0 | 1 | N | 23 | [21 - 30] | |
| 01/23/24 | 7/7/2023 | 7/11/2023 | 113561139 | A1A30A-KGJ | 1 | 0 | 1 | N | 22 | [21 - 30] | |
| 01/23/24 | 7/8/2023 | 7/12/2023 | 113705782 | A1A77A-KGJ | 10 | 5 | 5 | N | 21 | [21 - 30] | |
| 01/23/24 | 7/9/2023 | 7/13/2023 | 113725103 | N7L37A | 1 | 1 | 0 | N | 21 | [21 - 30] | |
| 01/23/24 | 7/10/2023 | 7/14/2023 | 113900946 | 2Z600F-KGJ | 1 | 0 | 1 | N | 20 | [11 - 20] | |
| 01/23/24 | 7/11/2023 | 7/15/2023 | 113933893 | 3JA05AN-150 | 1 | 0 | 1 | N | 19 | [11 - 20] | |
| 01/23/24 | 7/12/2023 | 7/16/2023 | 113969730 | A1A56A-KGJ | 1 | 0 | 1 | N | 18 | [11 - 20] | |
| 01/23/24 | 7/13/2023 | 7/17/2023 | 113987750 | 5RA32F-KGJ | 1 | 1 | 0 | N | 17 | [11 - 20] | |
| 01/23/24 | 7/14/2023 | 7/18/2023 | 114002150 | 2Z613F-KGJ | 2 | 1 | 1 | N | 16 | [11 - 20] | |
| 01/23/24 | 7/15/2023 | 7/19/2023 | 114002150 | 5RA32F-KGJ | 1 | 1 | 0 | N | 15 | [11 - 20] | |
| 01/23/24 | 7/16/2023 | 7/20/2023 | 114003452 | 2Z600F-KGJ | 5 | 2 | 3 | N | 15 | [11 - 20] | |
| 01/23/24 | 7/17/2023 | 7/21/2023 | 114005180 | 2Z600F-KGJ | 1 | 1 | 0 | N | 14 | [11 - 20] | |
| 01/23/24 | 7/18/2023 | 7/22/2023 | 114016353 | A1A77A-KGJ | 1 | 0 | 1 | N | 13 | [11 - 20] | |
| 01/23/24 | 7/19/2023 | 7/23/2023 | 110113433 | 3GZ25A-KGJ | 1 | 0 | 1 | Y | 12 | [11 - 20] | |
| 01/23/24 | 7/20/2023 | 7/24/2023 | 110113433 | K5L35A | 1 | 1 | 0 | Y | 11 | [11 - 20] | |
| 01/23/24 | 7/21/2023 | 7/25/2023 | 110113433 | 3GZ25A-KGJ | 1 | 0 | 1 | Y | 10 | [6 - 10] | |
| 01/23/24 | 7/22/2023 | 7/26/2023 | 110113433 | K5L35A | 1 | 1 | 0 | Y | 9 | [6 - 10] | |
| 01/23/24 | 7/23/2023 | 7/27/2023 | 110113433 | Y3D03A | 1 | 1 | 0 | Y | 9 | [6 - 10] | |
| 01/23/24 | 7/24/2023 | 7/28/2023 | 110113433 | Z7C07A | 1 | 1 | 0 | Y | 8 | [6 - 10] | |
| 01/23/24 | 7/25/2023 | 7/29/2023 | 110113433 | 3PZ35A-KGJ | 1 | 1 | 0 | Y | 7 | [6 - 10] | |
| 01/23/24 | 7/26/2023 | 7/30/2023 | 110113433 | D7P27A | 1 | 1 | 0 | Y | 6 | [6 - 10] | |
| 01/23/24 | 7/27/2023 | 7/31/2023 | 110113433 | 3PZ35A-KGJ | 1 | 1 | 0 | Y | 5 | [0 - 5] | |
| 01/23/24 | 7/28/2023 | 8/1/2023 | 110113433 | D7P27A | 1 | 0 | 1 | Y | 4 | [0 - 5] | |
| 01/23/24 | 7/29/2023 | 8/2/2023 | 110433907 | 3GZ25A-KGJ | 1 | 0 | 1 | Y | 3 | [0 - 5] | |
| 01/23/24 | 7/30/2023 | 8/3/2023 | 110433907 | K5L35A | 1 | 1 | 0 | Y | 3 | [0 - 5] | |
| 01/23/24 | 7/31/2023 | 8/4/2023 | 110433907 | Y3D03A | 1 | 1 | 0 | Y | 2 | [0 - 5] | |
| 01/23/24 | 8/1/2023 | 8/5/2023 | 110433907 | Z7C07A | 1 | 1 | 0 | Y | 1 | [0 - 5] | |
| 01/23/24 | 8/2/2023 | 8/6/2023 | 110433907 | 3GZ25A-KGJ | 1 | 0 | 1 | Y | 0 | [0 - 5] | |
The pivot chart would facilitate presenging ageing trend.
