cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jao1310
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 LatestDateLegal Declaration Date Status
Latest11/02/202111/02/2021Reported
Latest11/06/202111/02/2021Assessed

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
v-yangliu-msft
Community Support
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

vyangliumsft_0-1641266088828.png

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

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. 

bcdobbs
Resident Rockstar
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.

jao1310
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. 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors