Forum Discussion

dreyes6's avatar
dreyes6
Helper I
4 years ago
Solved

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 Pric...
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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
    _output

    Best Regards,
    Gao
    Community Support Team

     

    If 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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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?

dreyes6's avatar
dreyes6
Helper I
4 years ago

actually the data is like this,

 

IDNameEffective DateIMG
1A6/1/2021BIKE
1A6/1/2020GOAT
1A6/1/2022BIKE
2B1/1/2020TAXI
2B6/1/2022TAXI
3C1/1/2020Notes
3C8/1/2019Notes
3C6/1/2022Cycle
3C6/1/2020Cycle

 

 

 

and I wan to have another calculated column that will show complete data for IMG, so the output is like this


IDNameEffective DateIMGIMG OUTPUT
1A6/1/2021BIKEBIKE
1A6/1/2020GOATBIKE
1A6/1/2022BIKEBIKE
2B1/1/2020TAXITAXI
2B6/1/2022TAXITAXI
3C1/1/2020NotesCycle
3C8/1/2019NotesCycle
3C6/1/2022CycleCycle
3C6/1/2020CycleCycle
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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
    _output

    Best Regards,
    Gao
    Community Support Team

     

    If 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