Forum Discussion
ENDOFYEAR help
I attached a very basic file, I have 2 columns; a property and a purchase date.
Why can't I get the end of year date for purchase?
Here are screenshots as well:
Data:
Table:
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
5 Replies
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)
Ok, so how do I get the end of year for these dates?
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..
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
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!
