cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MelbourneBadger
Helper I
Helper I

End of Month Date Table

Hi all, 

 

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

Thank you very much before-hand. 

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

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 ( 2019131 ),
                [Value]
            )
    )

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

5 REPLIES 5
MelbourneBadger
Helper I
Helper I

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
Helper I
Helper I

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

ChrisMendoza
Super User I
Super User I

@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])

 image.png






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



mahoneypat
Super User IV
Super User IV

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 ( 2019131 ),
                [Value]
            )
    )

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors