Excel Power Query & Power Pivot: Ageing Trend

jajatidev

Board Regular
Joined
Jul 29, 2016
Messages
75
Office Version
  1. 365
Platform
  1. Windows
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;

  1. Ageing
  2. Age Group
  3. % of backlog by age group by extracted date
  4. % running total by extracted date
I have achieved the 1st two calculations. However, I am unable to execute steps 3 & 4. Hence, I am looking for assistance to get the calculations in place to get the output as a pivot chart.

Report DateReceiveDateCustomerReqDateSalesOrderNoProductNoOrderQtyConfirmedQtyUnconfirmedQtyCompleteDeliveryAgeingAgeGroup
01/22/2406/22/202309/15/20231129353801NR73A-K1H101N35[31 - 60]
01/22/2407/03/202307/07/2023113430548A1A30A-KGJ101N26[21 - 30]
01/22/2407/04/202307/08/20231134937101NR73A-K1H110N25[21 - 30]
01/22/2407/05/202307/09/2023113512547A1A30A-KGJ101N24[21 - 30]
01/22/2407/06/202307/10/20231135438135RA32F-KGJ101N23[21 - 30]
01/22/2407/07/202307/11/2023113561139A1A30A-KGJ101N22[21 - 30]
01/22/2407/08/202307/12/2023113705782A1A77A-KGJ1055N21[21 - 30]
01/22/2407/09/202307/13/2023113725103N7L37A110N21[21 - 30]
01/22/2407/10/202307/14/20231139009462Z600F-KGJ101N20[11 - 20]
01/22/2407/11/202307/15/20231139338933JA05AN-150101N19[11 - 20]
01/22/2407/12/202307/16/2023113969730A1A56A-KGJ101N18[11 - 20]
01/22/2407/13/202307/17/20231139877505RA32F-KGJ110N17[11 - 20]
01/22/2407/14/202307/18/20231140021502Z613F-KGJ211N16[11 - 20]
01/22/2407/15/202307/19/20231140021505RA32F-KGJ110N15[11 - 20]
01/22/2407/16/202307/20/20231140034522Z600F-KGJ523N15[11 - 20]
01/22/2407/17/202307/21/20231140051802Z600F-KGJ110N14[11 - 20]
01/22/2407/18/202307/22/2023114016353A1A77A-KGJ101N13[11 - 20]
01/22/2407/19/202307/23/20231101134333GZ25A-KGJ101Y12[11 - 20]
01/22/2407/20/202307/24/2023110113433K5L35A110Y11[11 - 20]
01/22/2407/21/202307/25/20231101134333GZ25A-KGJ101Y10[6 - 10]
01/22/2407/22/202307/26/2023110113433K5L35A110Y9[6 - 10]
01/22/2407/23/202307/27/2023110113433Y3D03A110Y9[6 - 10]
01/22/2407/24/202307/28/2023110113433Z7C07A110Y8[6 - 10]
01/22/2407/25/202307/29/20231101134333PZ35A-KGJ110Y7[6 - 10]
01/22/2407/26/202307/30/2023110113433D7P27A110Y6[6 - 10]
01/22/2407/27/202307/31/20231101134333PZ35A-KGJ110Y5[0 - 5]
01/22/2407/28/202308/01/2023110113433D7P27A101Y4[0 - 5]
01/22/2407/29/202308/02/20231104339073GZ25A-KGJ101Y3[0 - 5]
01/22/2407/30/202308/03/2023110433907K5L35A110Y3[0 - 5]
01/22/2407/31/202308/04/2023110433907Y3D03A110Y2[0 - 5]
01/22/2408/01/202308/05/2023110433907Z7C07A110Y1[0 - 5]
01/22/2408/02/202308/06/20231104339073GZ25A-KGJ101Y0[0 - 5]
01/23/246/22/20239/15/20231129353801NR73A-K1H101N35[31 - 60]
01/23/247/3/20237/7/2023113430548A1A30A-KGJ101N26[21 - 30]
01/23/247/4/20237/8/20231134937101NR73A-K1H110N25[21 - 30]
01/23/247/5/20237/9/2023113512547A1A30A-KGJ101N24[21 - 30]
01/23/247/6/20237/10/20231135438135RA32F-KGJ101N23[21 - 30]
01/23/247/7/20237/11/2023113561139A1A30A-KGJ101N22[21 - 30]
01/23/247/8/20237/12/2023113705782A1A77A-KGJ1055N21[21 - 30]
01/23/247/9/20237/13/2023113725103N7L37A110N21[21 - 30]
01/23/247/10/20237/14/20231139009462Z600F-KGJ101N20[11 - 20]
01/23/247/11/20237/15/20231139338933JA05AN-150101N19[11 - 20]
01/23/247/12/20237/16/2023113969730A1A56A-KGJ101N18[11 - 20]
01/23/247/13/20237/17/20231139877505RA32F-KGJ110N17[11 - 20]
01/23/247/14/20237/18/20231140021502Z613F-KGJ211N16[11 - 20]
01/23/247/15/20237/19/20231140021505RA32F-KGJ110N15[11 - 20]
01/23/247/16/20237/20/20231140034522Z600F-KGJ523N15[11 - 20]
01/23/247/17/20237/21/20231140051802Z600F-KGJ110N14[11 - 20]
01/23/247/18/20237/22/2023114016353A1A77A-KGJ101N13[11 - 20]
01/23/247/19/20237/23/20231101134333GZ25A-KGJ101Y12[11 - 20]
01/23/247/20/20237/24/2023110113433K5L35A110Y11[11 - 20]
01/23/247/21/20237/25/20231101134333GZ25A-KGJ101Y10[6 - 10]
01/23/247/22/20237/26/2023110113433K5L35A110Y9[6 - 10]
01/23/247/23/20237/27/2023110113433Y3D03A110Y9[6 - 10]
01/23/247/24/20237/28/2023110113433Z7C07A110Y8[6 - 10]
01/23/247/25/20237/29/20231101134333PZ35A-KGJ110Y7[6 - 10]
01/23/247/26/20237/30/2023110113433D7P27A110Y6[6 - 10]
01/23/247/27/20237/31/20231101134333PZ35A-KGJ110Y5[0 - 5]
01/23/247/28/20238/1/2023110113433D7P27A101Y4[0 - 5]
01/23/247/29/20238/2/20231104339073GZ25A-KGJ101Y3[0 - 5]
01/23/247/30/20238/3/2023110433907K5L35A110Y3[0 - 5]
01/23/247/31/20238/4/2023110433907Y3D03A110Y2[0 - 5]
01/23/248/1/20238/5/2023110433907Z7C07A110Y1[0 - 5]
01/23/248/2/20238/6/20231104339073GZ25A-KGJ101Y0[0 - 5]


The pivot chart would facilitate presenging ageing trend.

1706017750764.png
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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;

  1. Ageing
  2. Age Group
  3. % of backlog by age group by extracted date
  4. % running total by extracted date
I have achieved the 1st two calculations. However, I am unable to execute steps 3 & 4. Hence, I am looking for assistance to get the calculations in place to get the output as a pivot chart.

Report DateReceiveDateCustomerReqDateSalesOrderNoProductNoOrderQtyConfirmedQtyUnconfirmedQtyCompleteDeliveryAgeingAgeGroup
01/22/2406/22/202309/15/20231129353801NR73A-K1H101N35[31 - 60]
01/22/2407/03/202307/07/2023113430548A1A30A-KGJ101N26[21 - 30]
01/22/2407/04/202307/08/20231134937101NR73A-K1H110N25[21 - 30]
01/22/2407/05/202307/09/2023113512547A1A30A-KGJ101N24[21 - 30]
01/22/2407/06/202307/10/20231135438135RA32F-KGJ101N23[21 - 30]
01/22/2407/07/202307/11/2023113561139A1A30A-KGJ101N22[21 - 30]
01/22/2407/08/202307/12/2023113705782A1A77A-KGJ1055N21[21 - 30]
01/22/2407/09/202307/13/2023113725103N7L37A110N21[21 - 30]
01/22/2407/10/202307/14/20231139009462Z600F-KGJ101N20[11 - 20]
01/22/2407/11/202307/15/20231139338933JA05AN-150101N19[11 - 20]
01/22/2407/12/202307/16/2023113969730A1A56A-KGJ101N18[11 - 20]
01/22/2407/13/202307/17/20231139877505RA32F-KGJ110N17[11 - 20]
01/22/2407/14/202307/18/20231140021502Z613F-KGJ211N16[11 - 20]
01/22/2407/15/202307/19/20231140021505RA32F-KGJ110N15[11 - 20]
01/22/2407/16/202307/20/20231140034522Z600F-KGJ523N15[11 - 20]
01/22/2407/17/202307/21/20231140051802Z600F-KGJ110N14[11 - 20]
01/22/2407/18/202307/22/2023114016353A1A77A-KGJ101N13[11 - 20]
01/22/2407/19/202307/23/20231101134333GZ25A-KGJ101Y12[11 - 20]
01/22/2407/20/202307/24/2023110113433K5L35A110Y11[11 - 20]
01/22/2407/21/202307/25/20231101134333GZ25A-KGJ101Y10[6 - 10]
01/22/2407/22/202307/26/2023110113433K5L35A110Y9[6 - 10]
01/22/2407/23/202307/27/2023110113433Y3D03A110Y9[6 - 10]
01/22/2407/24/202307/28/2023110113433Z7C07A110Y8[6 - 10]
01/22/2407/25/202307/29/20231101134333PZ35A-KGJ110Y7[6 - 10]
01/22/2407/26/202307/30/2023110113433D7P27A110Y6[6 - 10]
01/22/2407/27/202307/31/20231101134333PZ35A-KGJ110Y5[0 - 5]
01/22/2407/28/202308/01/2023110113433D7P27A101Y4[0 - 5]
01/22/2407/29/202308/02/20231104339073GZ25A-KGJ101Y3[0 - 5]
01/22/2407/30/202308/03/2023110433907K5L35A110Y3[0 - 5]
01/22/2407/31/202308/04/2023110433907Y3D03A110Y2[0 - 5]
01/22/2408/01/202308/05/2023110433907Z7C07A110Y1[0 - 5]
01/22/2408/02/202308/06/20231104339073GZ25A-KGJ101Y0[0 - 5]
01/23/246/22/20239/15/20231129353801NR73A-K1H101N35[31 - 60]
01/23/247/3/20237/7/2023113430548A1A30A-KGJ101N26[21 - 30]
01/23/247/4/20237/8/20231134937101NR73A-K1H110N25[21 - 30]
01/23/247/5/20237/9/2023113512547A1A30A-KGJ101N24[21 - 30]
01/23/247/6/20237/10/20231135438135RA32F-KGJ101N23[21 - 30]
01/23/247/7/20237/11/2023113561139A1A30A-KGJ101N22[21 - 30]
01/23/247/8/20237/12/2023113705782A1A77A-KGJ1055N21[21 - 30]
01/23/247/9/20237/13/2023113725103N7L37A110N21[21 - 30]
01/23/247/10/20237/14/20231139009462Z600F-KGJ101N20[11 - 20]
01/23/247/11/20237/15/20231139338933JA05AN-150101N19[11 - 20]
01/23/247/12/20237/16/2023113969730A1A56A-KGJ101N18[11 - 20]
01/23/247/13/20237/17/20231139877505RA32F-KGJ110N17[11 - 20]
01/23/247/14/20237/18/20231140021502Z613F-KGJ211N16[11 - 20]
01/23/247/15/20237/19/20231140021505RA32F-KGJ110N15[11 - 20]
01/23/247/16/20237/20/20231140034522Z600F-KGJ523N15[11 - 20]
01/23/247/17/20237/21/20231140051802Z600F-KGJ110N14[11 - 20]
01/23/247/18/20237/22/2023114016353A1A77A-KGJ101N13[11 - 20]
01/23/247/19/20237/23/20231101134333GZ25A-KGJ101Y12[11 - 20]
01/23/247/20/20237/24/2023110113433K5L35A110Y11[11 - 20]
01/23/247/21/20237/25/20231101134333GZ25A-KGJ101Y10[6 - 10]
01/23/247/22/20237/26/2023110113433K5L35A110Y9[6 - 10]
01/23/247/23/20237/27/2023110113433Y3D03A110Y9[6 - 10]
01/23/247/24/20237/28/2023110113433Z7C07A110Y8[6 - 10]
01/23/247/25/20237/29/20231101134333PZ35A-KGJ110Y7[6 - 10]
01/23/247/26/20237/30/2023110113433D7P27A110Y6[6 - 10]
01/23/247/27/20237/31/20231101134333PZ35A-KGJ110Y5[0 - 5]
01/23/247/28/20238/1/2023110113433D7P27A101Y4[0 - 5]
01/23/247/29/20238/2/20231104339073GZ25A-KGJ101Y3[0 - 5]
01/23/247/30/20238/3/2023110433907K5L35A110Y3[0 - 5]
01/23/247/31/20238/4/2023110433907Y3D03A110Y2[0 - 5]
01/23/248/1/20238/5/2023110433907Z7C07A110Y1[0 - 5]
01/23/248/2/20238/6/20231104339073GZ25A-KGJ101Y0[0 - 5]


The pivot chart would facilitate presenging ageing trend.

View attachment 105564

Hi @smozgur, @ExcelToDAX, @GraH,
Would you be able to guide me on this request?
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top