Return a value associated to a date with criteria

[MA_guy_VER]

Hi - It's the new guy again... 

I am looking to return the value associated with a date. 

In this example, you can see in yellow the areas I am in need of using:  If 6 Weeks = 1, Find the MAX date (August 21) from that grouping and then return the PROJ BAL (84) associated with that date.

Thank you in advance once again.  This community is so helpful!

[MA_guy_VER]

@amitchandak My apologies if it is not clear.

With the above example, I want to filter the data on 6 Week and only take the "1"s.  Then from the grouping of the "1" I want to get the MAX Date from that group (Curr Cust Ship).  Once the MAX date is determined, I want to return the value in the PROJ BAL column associated with the MAX Date.  Based on the above, the MAX date is August 21, 2022 and the value that should return is 84.  The end result should be returning the number 84 in this example.  Thank you and I hope this makes the ask more clear.

[v-jingzhang · Accepted solution]

Hi @MA_guy_VER 

You can create a measure with below code. I assume that you want to get the [PROJ BAL] value of every item on their associated maximum date. To display the result, you can put the measure into a table along with the [Item #] column. 

Result =
VAR _maxDate =
    CALCULATE (
        MAX ( 'Table'[Curr Cust Ship] ),
        ALLEXCEPT ( 'Table', 'Table'[Item #] ),
        'Table'[6 Weeks] = 1
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[PROJ BAL] ),
        ALLEXCEPT ( 'Table', 'Table'[Item #] ),
        'Table'[Curr Cust Ship] = _maxDate
    )

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[MA_guy_VER]

@v-jingzhang Nailed it!  Thank you very much for the assistance.  Much appreciated!

[amitchandak]

@MA_guy_VER , The information you have provided is not making the problem clear to me. Can you please explain with an example.

You can try a measure like

calculate(Sum(Table[PROJ BAL]), filter(Table, [6 Weeks] = 1 )) 

Plot with dates and other column

Appreciate your Kudos.