Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dreyes6
Helper I
Helper I

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


IDName IMAGEPriceIMAGE OUTPUT
1A6/1/2021BIKE BIKE
1A6/1/2020GOAT BIKE
1A6/1/2022BIKE7750BIKE
2B1/1/2020TAXI TAXI
2B6/1/2022TAXI150TAXI
3C1/1/2020Notes Cycle
3C8/1/2019Notes Cycle
3C6/1/2022Cycle800Cycle
3C6/1/2020Cycle Cycle
1 ACCEPTED SOLUTION

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

vcgaomsft_0-1658307605525.png

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

EylesIT_0-1656490803382.png

Then click the little Down arrow to the right of Column3 and select Latest

EylesIT_1-1656490855198.png

 

With your sample data this gives me:

EylesIT_2-1656490873334.png

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?

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

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

vcgaomsft_0-1658307605525.png

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

My Bad, here you go thank u

 

IDNameEffective DateIMGPriceIMG OUTPUT
1A6/1/2021BIKE BIKE
1A6/1/2020GOAT BIKE
1A6/1/2022BIKE7750BIKE
2B1/1/2020TAXI TAXI
2B6/1/2022TAXI150TAXI
3C1/1/2020Notes Cycle
3C8/1/2019Notes Cycle
3C6/1/2022Cycle800Cycle
3C6/1/2020Cycle Cycle
dreyes6
Helper I
Helper I

Appreciate anyone's help. Thanks a lot community

Anonymous
Not applicable

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:

EylesIT_0-1656492544553.png

 

 

 

You can then simply filter the IsLatest column to Y, which makes the table display this:

EylesIT_1-1656492581611.png

Is this what you are looking for?

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.