ENDOFYEAR help

[michaelu1]

I attached a very basic file, I have 2 columns; a property and a purchase date.

PBIX File 

Why can't I get the end of year date for purchase?

Here are screenshots as well:

Data:

Table:

[Anonymous · Accepted solution]

Hi @HotChilli ,thanks for the quick reply, I'll add further.

Hi @michaelu1 ,

Regarding your question, the ENDOFYEAR function groups your date columns according to the year, and then gets the largest date in it.You need to create a date table that contains the dates of an entire year.

1.Use the following DAX expression to create a table

Table = CALENDAR(DATE(1994,1,1),DATE(2025,12,31))

2.Use the following DAX expression to create a column in 'Table'

Column = ENDOFYEAR('Table'[Date])

3.Use the following DAX expression to create a column in Your own table.

Column = LOOKUPVALUE('Table'[Column],'Table'[Date],'Tabelle1'[Purchase Date])

4.Final output

[michaelu1]

@Anonymous That is certainly a solution.

I was hoping to avoid adding additional columns to my model.

Also, I was planning on using the endofyear in a measure which I'm sure your solution works for.

For now the solution that I came up works, but your idea will certainly be helpful for endofmonth scenarios, thank you!

[michaelu1]

In the end I used DATE(YEAR('Table'[Purchase Date]),12,31).

However, this isn't an ideal solution as it can't be applied to end of month since the month days end differently..

[HotChilli]

Time intelligence functions (like ENDOFYEAR) need a proper Dates table.  It won't work by passing a date from a Fact table.  In this case, it's looking for the EndOfYear in a list that contains one date (the date on the same row)

[michaelu1]

Ok, so how do I get the end of year for these dates?