Extract unique month end dates from range of daily dates?

[pholt33]

I have a fairly large date set with thousands of rows. Some dates have many records/rows, some dates have none, and some dates have a single row. What I am trying to do is extract a unique month end date for every date that is in the data set. I then want to make that unique data set a table in order to add to the data model.

C:C and E:E are the same except for one is a table. As you can see, once it converts to a table it loses the uniqueness and wants to show the month end for every single record.

Monarch - data v2.xlsx
	A	B	C	D	E	F	G
1	Date		MonthEndDate		MonthEndDate		MonthEndDate
2	3/30/2023		3/31/2023		3/31/2023		3/31/2023
3	3/31/2023		4/30/2023		3/31/2023		4/30/2023
4	4/1/2023		5/31/2023		4/30/2023		5/31/2023
5	4/1/2023				4/30/2023
6	4/2/2023				4/30/2023
7	5/6/2023				5/31/2023
8	5/6/2023				5/31/2023
9	5/6/2023				5/31/2023
10	5/6/2023				5/31/2023
Sheet2

Cell Formulas
Range	Formula
C2:C4	C2	=UNIQUE(EOMONTH(TEXT($A2:$A10,"M/D/YYYY"),0))
G2:G4	G2	=C2
E2:E10	E2	=@UNIQUE(EOMONTH(TEXT($A2:$A10,"M/D/YYYY"),0))
Dynamic array formulas.

How can I make this work so that it looks like G:G and will dynamically expand as the original daily data set grows?

 

[Fluff]

Once you put the formula into a table, then it will no longer grow/shrink as dates are added or removed.

 

[Herakles]

Is it just the dates that you want in a table and if so why?

If not then are there other columns to ba added to the table?

How often will the dates in column A be added to if at all?

 

[pholt33]

It's being used for reporting purposes. Daily balance data but only want to report values on month-end dates. My thought was that the month end date section could automatically expand as the daily data grows as time passes, which would then be used in the data model as a filter, and would make it so that I don't have to manually select the desired dates in a pivot table.

I'm new to using the Excel data model functionally so maybe there is a different way to achieve the same result? If I think in terms of a sql query, I would put some month end date logic in the WHERE clause to return only the data I want. Can I do something similar with the data model? I am totally open to alternative ideas from what I originally had in mind regarding how to automatically filter for dates.

 

[Fluff]

I've never needed to use the data model, so cannot help with that.

 

[Herakles]

How many months does the report cover?

For as many months that are covered by the data?

What are you reporting on against these month end dates?

Can you show us what the final report will look like?