Data for most recent date in Power BI for each year

Question

I have to group on agreement number and get the most recent data for each year. My end goal is to view most recent agreement number for each year so that i don't have repeating agreement number for the year. I am getting error while using SQL for direct query. How can i get this done in Power BI by other method?

consider a column as agreement number with repeating values and date for 5 years. enter image description here

Please don't post cellphone photos of your screen of data. Type in the data into the body of the question. It only takes a few lines to explain your secnario. — David Browne - Microsoft, Aug 18 '22 at 00:19

score 1 · Answer 1 · answered Aug 18 '22 at 05:31

Agreement	CreatedOn
11600401	1/1/2016
11600402	5/5/2016
11600403	8/8/2016
11600404	3/3/2015
11600405	4/4/2015
11600406	2/2/2015
11600407	7/7/2017
11600408	12/12/2017
11600409	5/5/2018
11600410	1/1/2018
11600408	3/3/2018

recent agreement number is the latest number in each year I assume without checking the date and the measure is a bit simple :

Measure = 

MAX ( 'Table'[Agreement] )

also add the Year part from [Createdon] to the table

@Pallavi Please accept as answer if it worked for you... – Umut K Aug 18 '22 at 12:27 — Umut K, Aug 18 '22 at 12:27

score 0 · Answer 2 · answered Aug 18 '22 at 01:58

0

In power query, you can separate the year from the date field, and group the table by agreement number and year, then return the max result so you can get the most recent date for each year.

answered Aug 18 '22 at 01:58

Penn

15
4

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 23 '22 at 06:44

Data for most recent date in Power BI for each year

2 Answers2