Forum Discussion
Find Latest Date
I'm trying to make an output that is looking for the latest date below is my data, there are names that have multiple image and I need the image with te latest date
| ID | Name | IMAGE | Price | IMAGE OUTPUT | |
| 1 | A | 6/1/2021 | BIKE | BIKE | |
| 1 | A | 6/1/2020 | GOAT | BIKE | |
| 1 | A | 6/1/2022 | BIKE | 7750 | BIKE |
| 2 | B | 1/1/2020 | TAXI | TAXI | |
| 2 | B | 6/1/2022 | TAXI | 150 | TAXI |
| 3 | C | 1/1/2020 | Notes | Cycle | |
| 3 | C | 8/1/2019 | Notes | Cycle | |
| 3 | C | 6/1/2022 | Cycle | 800 | Cycle |
| 3 | C | 6/1/2020 | Cycle | Cycle |
Hi dreyes6 ,
Please try this:
IMAGE OUTPUT = VAR _latest_date = CALCULATE ( MAX ( 'Table'[Effective Date] ), FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ) ) VAR _output = CALCULATE ( MAX ( 'Table'[IMG] ), FILTER ( 'Table', 'Table'[Effective Date] = _latest_date && 'Table'[ID] = EARLIER ( 'Table'[ID] ) ) ) RETURN _outputBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
6 Replies
Appreciate anyone's help. Thanks a lot community
Here is how you can add the IsLastest column:
Create a Calculated Column
IsLatest = VAR vImage = YourTable[IMAGE] VAR vLastDate = CALCULATE(MAX('YourTable'[Column3]), ALL(YourTable), YourTable[IMAGE] = vImage) RETURN IF('YourTable'[Column3] = vLastDate, "Y", "N")Then you can use a table visual to display all the data. Make sure you set all the fields to "Don't Summarize". This gives:
You can then simply filter the IsLatest column to Y, which makes the table display this:
Is this what you are looking for?
dreyes6 , you can use a table visual to display the latest date for each IMAGE. Just drag the column IMAGE and your Date column into a table visual. Your sample data has no name for column 3 so I'm calling it Column3.
Then click the little Down arrow to the right of Column3 and select Latest
With your sample data this gives me:
Is this what you are after?
I think what you are looking for is to have a new column in your table that indicates whether the row is the latest row for that image. For example a column called IsLatest which is either Y or N. Then your reports can simply filter for IsLatest = Y. I notice in your sample data that the Price column only seems to have a value for the latest rows for each image. Is it possible that when the Price field is not blank then that row is the latest for that image?
My Bad, here you go thank u
ID Name Effective Date IMG Price IMG OUTPUT 1 A 6/1/2021 BIKE BIKE 1 A 6/1/2020 GOAT BIKE 1 A 6/1/2022 BIKE 7750 BIKE 2 B 1/1/2020 TAXI TAXI 2 B 6/1/2022 TAXI 150 TAXI 3 C 1/1/2020 Notes Cycle 3 C 8/1/2019 Notes Cycle 3 C 6/1/2022 Cycle 800 Cycle 3 C 6/1/2020 Cycle Cycle actually the data is like this,
ID Name Effective Date IMG 1 A 6/1/2021 BIKE 1 A 6/1/2020 GOAT 1 A 6/1/2022 BIKE 2 B 1/1/2020 TAXI 2 B 6/1/2022 TAXI 3 C 1/1/2020 Notes 3 C 8/1/2019 Notes 3 C 6/1/2022 Cycle 3 C 6/1/2020 Cycle and I wan to have another calculated column that will show complete data for IMG, so the output is like this
ID Name Effective Date IMG IMG OUTPUT 1 A 6/1/2021 BIKE BIKE 1 A 6/1/2020 GOAT BIKE 1 A 6/1/2022 BIKE BIKE 2 B 1/1/2020 TAXI TAXI 2 B 6/1/2022 TAXI TAXI 3 C 1/1/2020 Notes Cycle 3 C 8/1/2019 Notes Cycle 3 C 6/1/2022 Cycle Cycle 3 C 6/1/2020 Cycle Cycle Hi dreyes6 ,
Please try this:
IMAGE OUTPUT = VAR _latest_date = CALCULATE ( MAX ( 'Table'[Effective Date] ), FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ) ) VAR _output = CALCULATE ( MAX ( 'Table'[IMG] ), FILTER ( 'Table', 'Table'[Effective Date] = _latest_date && 'Table'[ID] = EARLIER ( 'Table'[ID] ) ) ) RETURN _outputBest Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
