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