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 tr...
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.] )
    )
  • RafaelAri's avatar
    RafaelAri
    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

Thanks a lot, it did the work!