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

     

HotChilli's avatar
HotChilli
Community Champion
2 years ago

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

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