End of Month Date Table

[MelbourneBadger]

Hi all, 

How can I create a new date table but only consist of End of Month dates? 

Thank you very much before-hand. 

[faisalazh]

You can also create a new calculated table using this formula:

Calendar = filter( calendar( date(2022,1,1), date(2022,12,31)), [Date] = EOMONTH([Date], 0))

[MelbourneBadger]

Thank you for your reply @ChrisMendoza. I have done something similar to what you suggested but now I would like to the "unrelate" the new date table from an existing Calendar table. 

[MelbourneBadger]

Awesome! Just what I need. Thank you @mahoneypat !

[ChrisMendoza]

@MelbourneBadger -

From an existing Date Table as:

Date = CALENDAR(DATE(2019,01,01),TODAY())

Add a Column as :

EoM Column = ENDOFMONTH('Date'[Date])

Create new table as :

EoM = DISTINCT('Date'[EoM Column])

 

[mahoneypat · Accepted solution]

Here is one way to do it in a DAX calculated table.  You can adapt it to start at a different month or extend more or less.

Date EOMs =
VAR nums =
    GENERATESERIES (
        0,
        24,
        1
    )
RETURN
    ADDCOLUMNS (
        nums,
        "Date",
            EOMONTH (
                DATE ( 2019, 1, 31 ),
                [Value]
            )
    )

Regards,

Pat

[MelbourneBadger]

Hi @mahoneypat,

Is it possible to adjust the series generation such that it will be added month by month instead of having total number of months in the series already specifically defined?