Forum Discussion

michaelu1's avatar
michaelu1
Advocate II
2 years ago
Solved

ENDOFYEAR help

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: T...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
michaelu1
Advocate II
2 years ago

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!