Forum Discussion

RafaelAri's avatar
RafaelAri
Icon for Helper III rankHelper III
3 years ago
Solved

Get the most recent transaction date

Hello to all the experts,

I have a database, that contains Warehouses inventory.

The columns are: Item ID / Warehouse name / qty. in WH / Last transaction date.

My goal is:

Get the most recent transaction date, for items that has total quantity of 0, in all WH's, in which they are contained.

In this example: items 101, 104 and 106.

(Asume that the table is sorted by Item ID and Last Trans. date)

Item IDWHQty. in WHDate Last Trans. Item IDWHQty. in WHDate Last Trans.
101A002/01/23Results:101C024/02/23
101B017/01/23 104B005/01/23
101C024/02/23 106D030/03/23
102A12620/02/23     
103A1412/03/23     
104B005/01/23     
105B015/01/23     
105C4116/01/23     
106A020/03/23     
106C025/03/23     
106D030/03/23     
107A131/03/23     
107C001/04/23     
107D002/02/23     
108C1803/01/23     
Tutorial Requests
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi, Please check the below picture and the attached pbix file.

    It is for creating a new table.

     

     

     

    Expected table = 
VAR _expectedresulttable =
    CALCULATETABLE (
        Data,
        FILTER (
            VALUES ( Data[Item ID] ),
            CALCULATE ( ABS ( SUM ( Data[Qty. in WH] ) ) ) = 0
        )
    )
VAR _groupbylatestdate =
    GROUPBY (
        _expectedresulttable,
        Data[Item ID],
        "@latestdate", MAXX ( CURRENTGROUP (), Data[Date Last Trans.] )
    )
RETURN
    CALCULATETABLE (
        Data,
        TREATAS ( _groupbylatestdate, Data[Item ID], Data[Date Last Trans.] )
    )

6 Replies

    • RafaelAri's avatar
      RafaelAri
      Icon for Helper III rankHelper III
      3 years ago

      Thanks a lot, it did the work!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi, Please check the below picture and the attached pbix file.

    It is for creating a new table.

     

     

     

    Expected table = 
VAR _expectedresulttable =
    CALCULATETABLE (
        Data,
        FILTER (
            VALUES ( Data[Item ID] ),
            CALCULATE ( ABS ( SUM ( Data[Qty. in WH] ) ) ) = 0
        )
    )
VAR _groupbylatestdate =
    GROUPBY (
        _expectedresulttable,
        Data[Item ID],
        "@latestdate", MAXX ( CURRENTGROUP (), Data[Date Last Trans.] )
    )
RETURN
    CALCULATETABLE (
        Data,
        TREATAS ( _groupbylatestdate, Data[Item ID], Data[Date Last Trans.] )
    )
  • RafaelAri's avatar
    RafaelAri
    Icon for Helper III rankHelper III
    3 years ago

    I found some mistakes in this solution, but thanks anyway for your replay 👍