topic Re: ENDOFYEAR help in Desktop

ENDOFYEAR help

michaelu1 — Mon, 08 Apr 2024 15:12:01 GMT

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:

Re: ENDOFYEAR help

HotChilli — Mon, 08 Apr 2024 16:33:33 GMT

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)

Re: ENDOFYEAR help

michaelu1 — Mon, 08 Apr 2024 16:37:53 GMT

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

Re: ENDOFYEAR help

michaelu1 — Tue, 09 Apr 2024 13:19:57 GMT

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..

Re: ENDOFYEAR help

Anonymous — Thu, 11 Apr 2024 07:40:18 GMT

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

Re: ENDOFYEAR help

michaelu1 — Thu, 11 Apr 2024 13:14:36 GMT

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!