topic Re: Getting the latest date based on the selected year in DAX Commands and Tips

Getting the latest date based on the selected year

Anonymous — Sun, 02 Jan 2022 00:31:59 GMT

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

Re: Getting the latest date based on the selected year

bcdobbs — Sun, 02 Jan 2022 09:37:31 GMT

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.

Re: Getting the latest date based on the selected year

Anonymous — Mon, 03 Jan 2022 03:36:01 GMT

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.

Re: Getting the latest date based on the selected year

Anonymous — Tue, 04 Jan 2022 03:15:21 GMT

Hi Anonymous ,

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

Re: Getting the latest date based on the selected year

Anonymous — Sat, 15 Jan 2022 02:47:32 GMT

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.