cancel
Showing results for
Did you mean:
Frequent Visitor

## Getting the latest date based on the selected year

Hi,

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.

Is Latest =
VAR Max_Date =
CALCULATE(MAX(Table1[Legal Declaration date]),
FILTER(Table1,Table1[Entity ID]=EARLIER(Table1[Entity ID]))
)
RETURN
IF(Table1[Date]>Max_Date,"Latest","Previous")

 Is Latest Date Legal Declaration Date Status Latest 11/02/2021 11/02/2021 Reported Latest 11/06/2021 11/02/2021 Assessed

Note: The Date column refers to the reported/assessed date, while the legal declaration date refers to the date when the report was submitted.

Will appreciate your help on this. Thank you

4 REPLIES 4
Community Support

Hi  @jao1310 ,

Here are the steps you can follow：

1. Create measure.

``````Ls_latest_measure =
var _select=SELECTEDVALUE('Dates'[Year])
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])))
return
IF(MAX('Tbl_Data'[Lodged date]) > _seletdate, "Latest" , "Previous" )``````

2. Result:

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

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Frequent Visitor

Hi  Liu Yang

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.

Resident Rockstar

Not 100% sure what you're trying to achieve. Are you able to share a demo pbix file.

My first thought is you're trying to use a calculated column which is processed when data is loaded and doesn't change values based on a filter context from a slicer.

Frequent Visitor

Hi

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.

Announcements