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

 

 

 

 

 

 

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.

3 Replies

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