Getting the latest date based on the selected year
I have two date columns, same as the example below. I'm trying to get the latest Date based on the selected year from my year slicer and show either "Latest" or "Previous". My current dax formula is returning "Latest" even though one of dates is obviously not the latest date. below is my formula that I added as a calculated column.
var _seletdate=CALCULATE(MAX('Tbl_Data'[Declaration date]),FILTER(ALL('Tbl_Data'),YEAR('Tbl_Data'[Declaration date])=_select&&'Tbl_Data'[Entity name]=MAX('Tbl_Data'[Entity name])))
IF(MAX('Tbl_Data'[Lodged date]) > _seletdate, "Latest" , "Previous" )
According to the slicer selection 2019, the maximum date obtained is 2019-2-13, and the current [Lodged date] is 2020-2-13 and 2020-2-11, both of which are larger than the maximum date obtained, so it is Latest
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Getting the latest date based on the selected year.pbix
Thanks for your responding, I appreciate it very much.
I think we're close. Sorry I made a mistake with my explanation of what I'm trying to do. The table should show "Latest' on the latest date 'assessed' (not 'lodged').
Based on the table below, the Revision should come up as the latest because it has the latest assesed date (6/24/2021), the other ones should be previous. I tried to use your fomula and just replaced the 'lodged date' with 'assessed date' but I'm still getting 'Latest' for both entry.
Thank you for your immediate response. Our data is a bit weird because we have two versions of data in the same table. We have the orginal version and a revision which a company reports within 12 months after the orginal report was submitted. I want my table visual to show whether the data is "Latest"or "Previous".
The dates in the lodged date column and declaration column are often the same but it does vary sometimes when a company makes another revision. In this case the declaration date will remain the same whilst the lodge date will be more recent date.
I have a calculated column which gets the max date in the "Daclaration date" column. And tryign to compare it with the Lodged date column. If the lodged date column is >= declration date then my calculated column will return "Latest"otherwise "Previous".
I'd like to return the "latest" value based on the year selected in the year slicer. My current formula returns the max date for the entire column regardless the year. Therefore, there's no issue when I select the most recent year.
I hope I explained that clearly. Also, here is the link to the .pbix file and data.
I appreciate whatever advice you can give. Thanks again.