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 status ends with "\1" and the date for that status is the most recent, for that item.

See an example for 1 specific item in the picture:

 

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.

11 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Share data in a format that can be pasted in an MS Excel file.  For that dataset, show the expected result very clearly.

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi RafaelAri - you can create a measure that identifies the latest entry for each item with a status ending in "\1" and then sums the quantity for those entries. 

    write dax logic as below:

    Latest_Qty_With_Status_1 =
    VAR LatestDatePerItem =
    CALCULATE(
    MAX('Table'[Status Date]),
    FILTER(
    'Table',
    RIGHT('Table'[Status], 2) = "\1"
    )
    )
    RETURN
    SUMX(
    FILTER(
    'Table',
    RIGHT('Table'[Status], 2) = "\1" &&
    'Table'[Status Date] = LatestDatePerItem
    ),
    'Table'[Qty]
    )

     

    you can change 'Table' with the actual name of your table in Power BI.

     

    Hope this logic helps. 

    • RafaelAri's avatar
      RafaelAri
      Helper III
      1 year ago

      Thanks, this is helpful, I'm trying to use this solution

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

  • RafaelAri's avatar
    RafaelAri
    Helper III
    1 year ago

    Thanks , but I don't get the results.
    I see that I get results in my visual even if the Status with "\1" is not the latest.

    It looks like the MAX with the FILTER are not correct 

  • Rupak_bi's avatar
    Rupak_bi
    Super User
    1 year ago

    Hi RafaelAri ,

     

    Try below dax. 

     

    result= 

    var max_date = calculate(max(table,status date),allexcept(table, item))
    return
    calculate(sum(table,qty),allexcept(table,item),status date = max_date, RIGHT(table,status, 2) = "\1"))

    alternate dax:

    result= 

    var max_date = calculate(max(table,status date),allexcept(table, item))
    return
    sumx(filter(allexcept(table,item),status date = max_date&& right(table,status,2)="\1"),qty)

     

     

    both should work



    • RafaelAri's avatar
      RafaelAri
      Helper III
      1 year ago

      Thanks, I tryed this solution, but it is not what I need

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    you can create a column to mark if need to filter

     

    Column = IF(RIGHT('Table'[Status],2)="\1"&&'Table'[Status Date]<>CALCULATE(max('Table'[Status Date]),ALLEXCEPT('Table','Table'[Item])),"N","Y")
     
     
    then you can only sum those data that column ="Y"
  • Anonymous's avatar
    Anonymous
    Not applicable
    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.