Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Marking Last Date based on 2 Criteria

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 MACH...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
5 years ago

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    5 years ago

    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.