Forum Discussion

RafaelAri's avatar
RafaelAri
Helper III
1 year ago
Solved

Get latest date values

Hello, I have a table of items, with a "Status" and "Status Date" columns, of different statuses, for the same item. It is required to build a measure that will summarize the "Qty" field, In case a...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi RafaelAri ,

    Thanks for all the replies!
    And RafaelAri , I think rajendraongole1's reply is close, so I modified his response a bit:
    Here is my sample data:


    I changed his DAX into this:

    Latest_Qty_With_Status_1 = 
VAR _RecentDate = 
CALCULATE(
    MAX('Table'[Status Date]),
    FILTER(
        ALL('Table'),
        RIGHT('Table'[Status], 2) = "\1" && 'Table'[Item] = MAX('Table'[Item])
    )
)
RETURN
CALCULATE(
    SUM('Table'[Qty.]),
    ALL('Table'),
    RIGHT('Table'[Status], 2) = "\1" && 'Table'[Item] = MAX('Table'[Item]) && 'Table'[Status Date] = _RecentDate
)

    And the final output is as below:


    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

danextian's avatar
danextian
Super User
1 year ago

Hi RafaelAri 

Assuming the status is to be calculted by Status and Item columns, try this measure:

MyMeasure =

VAR __LATEST_STATUS_DATE =
    CALCULATE (
        MAX ( 'table'[Status Date] ),
        ALLEXCEPT ( 'table', 'table'[Status], 'table'[Item] )
    )
RETURN
    CALCULATE (
        SUM ( 'table'[Qty.] ),
        KEEPFILTERS ( 'table'[Status Date] = __LATEST_STATUS_DATE )
    )

 

If you want a calculated column to be used in a slicer/as a filter:

Is Latest=
VAR __LATEST_STATUS_DATE =
    CALCULATE (
        MAX ( 'table'[Status Date] ),
        ALLEXCEPT ( 'table', 'table'[Status], 'table'[Item] )
    )
RETURN
    table'[Status Date] = __LATEST_STATUS_DATE  --will return TRUE/FALSE

 

  • RafaelAri's avatar
    RafaelAri
    Helper III
    1 year ago

    Thanks, I tried but didn't get the results I expected.