Formula to get last value of month

[Greg123]

I have a worksheet like this one. I am looking for a formula that will return the last production number for each month into the table on the right. However, not every date is listed so the formula can not simply look for the last day of the month (e.g. 3/31/19) because that date might not be listed at all. Any help is appreciated!

	A	B	C	D	E	F	G
1	Date	Day	Production			Month	Production
2	01/02/19	Wed				Jan
3	01/04/19	Fri	38,891			Feb
4	01/15/19	Tue				Mar
5	01/16/19	Wed				Apr
6	01/17/19	Thu	90,572			May
7	01/23/19	Wed				Jun
8	01/24/19	Thu	147,728			Jul
9	01/31/19	Thu	35,279			Aug
10	02/01/19	Fri				Sep
11	02/04/19	Mon				Oct
12	02/05/19	Tue	28,165			Nov
13	02/14/19	Thu	44,083			Dec
14	02/22/19	Fri
15	02/25/19	Mon	28,545
16	02/26/19	Tue	5,329
17	02/27/19	Wed
18	02/28/19	Thu
19	03/01/19	Fri	19,410
20	03/04/19	Mon	55,347
21	03/25/19	Mon	72,419
22	03/26/19	Tue	14,509
23	03/28/19	Thu
24	03/29/19	Fri
25	04/01/19	Mon	12,539
26	04/02/19	Tue	44,113
27	04/03/19	Wed
28	04/04/19	Thu
29	04/05/19	Fri	37,608
30	04/08/19	Mon	38,097
31

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[AhoyNC]

Maybe something like this:
This is an array formula and must be entered with CTRL-SHIFT-ENTER. Then drag formula down.

Excel Workbook
	A	B	C	D	E	F	G
1	Date	Day	Production			Month	Production
2	1/2/2019	Wed				Jan	35,279
3	1/4/2019	Fri	38,891			Feb	5,329
4	1/15/2019	Tue				Mar	14,509
5	1/16/2019	Wed				Apr	38,097
6	1/17/2019	Thu	90,572			May
7	1/23/2019	Wed				Jun
8	1/24/2019	Thu	147,728			Jul
9	1/31/2019	Thu	35,279			Aug
10	2/1/2019	Fri				Sep
11	2/4/2019	Mon				Oct
12	2/5/2019	Tue	28,165			Nov
13	2/14/2019	Thu	44,083			Dec
14	2/22/2019	Fri
15	2/25/2019	Mon	28,545
16	2/26/2019	Tue	5,329
17	2/27/2019	Wed
18	2/28/2019	Thu
19	3/1/2019	Fri	19,410
20	3/4/2019	Mon	55,347
21	3/25/2019	Mon	72,419
22	3/26/2019	Tue	14,509
23	3/28/2019	Thu
24	3/29/2019	Fri
25	4/1/2019	Mon	12,539
26	4/2/2019	Tue	44,113
27	4/3/2019	Wed
28	4/4/2019	Thu
29	4/5/2019	Fri	37,608
30	4/8/2019	Mon	38,097
Sheet

 

[sandy666]

alternative with PowerQuery (Get&Transform)

Date	Day	Production		Month Name	Production
02/01/2019​	Wed			January	35279​
04/01/2019​	Fri	38,891​		February	5329​
15/01/2019​	Tue			March	14509​
16/01/2019​	Wed			April	38097​
17/01/2019​	Thu	90,572​
23/01/2019​	Wed
24/01/2019​	Thu	147,728​
31/01/2019​	Thu	35,279​
01/02/2019​	Fri
04/02/2019​	Mon
05/02/2019​	Tue	28,165​
14/02/2019​	Thu	44,083​
22/02/2019​	Fri
25/02/2019​	Mon	28,545​
26/02/2019​	Tue	5,329​
27/02/2019​	Wed
28/02/2019​	Thu
01/03/2019​	Fri	19,410​
04/03/2019​	Mon	55,347​
25/03/2019​	Mon	72,419​
26/03/2019​	Tue	14,509​
28/03/2019​	Thu
29/03/2019​	Fri
01/04/2019​	Mon	12,539​
02/04/2019​	Tue	44,113​
03/04/2019​	Wed
04/04/2019​	Thu
05/04/2019​	Fri	37,608​
08/04/2019​	Mon	38,097​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Month = Table.AddColumn(Table.TransformColumnTypes(Source,{{"Date", type date}, {"Day", type text}, {"Production", Int64.Type}}), "Month Name", each Date.MonthName([Date]), type text),
    Group = Table.Group(Table.SelectRows(Month, each ([Production] <> null)), {"Month Name"}, {{"Max", each List.Max([Date]), type date}, {"Count", each _, type table}}),
    Expand = Table.ExpandTableColumn(Table.AddColumn(Group, "Custom", each Table.LastN([Count],1)), "Custom", {"Production"}, {"Production"}),
    ROC = Table.RemoveColumns(Expand,{"Count", "Max"})
in
    ROC[/SIZE]