Marking Last Date based on 2 Criteria

[Anonymous]

Good Afternoon...

I have a dataset that contains 2 key fields Machine and Sales Date and also a Date and Time Field.

I am looking to identify the LATEST END TIME where the SALES DATE and MACHINE Match.  This then should pull up the last record on that machine on that day. The was to make a collumn and id this as this 1 and any others 0 so I could refer to it in other areas later. 

Attempts so far have not worked. 

Any hints apprciated.

Andy

[AlexisOlson]

Is your dataset a single table? If not, what are all the relevant table and column names and relationships?

[Anonymous]

Sorry for not gettiing back sooner...  This table is a standalone alone table with only a relationship on the field MachineSiteJoin to another table for reference data.

What I am looking to do is the following.

There are multiple date / time records in the the field FridayEnd that will be ascending order, early to late. What I am looking to do is flag the latest FridayEnd when MachineSite Join and Friday End Capacity are the same. So the Last record (Date/Time) will be "Last" and the rest labelled as "Not Last". 

I was planning then to use the fields flagged as "latest" to do some further calcuations on.

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Based on your description, you can create a calculated column like this:

Flag =
VAR _latest =
    CALCULATE (
        MAX ( 'Table'[FridayEnd] ),
        FILTER (
            'Table',
            'Table'[MachineSite Join] = EARLIER ( 'Table'[MachineSite Join] )
                && 'Table'[Friday End Capacity] = EARLIER ( 'Table'[Friday End Capacity] )
        )
    )
RETURN
    IF ( 'Table'[FridayEnd] = _latest, "Last", "Not Last" )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.