Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
1 year ago
Solved

Get the latest data between 2 tables

Hello! I'm looking at how to bring the most recent value from one board to another.

I have a first table with the costs of entry of a merchandise, which is updated each time new merchandise arrives.

In another table I have the events, where all the transactions are shown and my intention is to keep the cost updated according to the product cost table shown above.

To do this, what must be met is that the product code is the same and that the cost date must be the most recent in the cost table.

What I am looking for is that the cost remains as a new column within the table of events, so that from there I can calculate the margins of the transactions.

Thank you very much in advance!

  • Syndicate_Admin's avatar
    Syndicate_Admin
    1 year ago

    Hello!

    Thank you very much for the answer. This one was useful but I had to add only one detail to LatestDate, since I wanted the date to be earlier than the date of the document's output.

    MostRecentCost = 
VAR LatestDate = 
    CALCULATE(
        MAX('CostTable'[Fecha]),
        FILTER(
            'CostTable',
            'CostTable'[CodProd] = 'EventsTable'[CodProd] &&
            'CostTable'[Fecha] <= 'EventsTable'[Date]
        )
    )
RETURN
    CALCULATE(
        MAX('CostTable'[CostoUnitario]),
        FILTER(
            'CostTable',
            'CostTable'[CodProd] = 'EventsTable'[CodProd] &&
            'CostTable'[Fecha] = LatestDate
        )
    )

    With that variable in the filter, it worked perfectly for me.

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Syndicate_Admin ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) My test data is the same as yours.

    (2) We can create a calculated column.

     

    MostRecentCost = 
VAR LatestDate = 
    CALCULATE(
        MAX('CostTable'[Fecha]),
        FILTER(
            'CostTable',
            'CostTable'[CodProd] = 'EventsTable'[CodProd]
        )
    )
RETURN
    CALCULATE(
        MAX('CostTable'[CostoUnitario]),
        FILTER(
            'CostTable',
            'CostTable'[CodProd] = 'EventsTable'[CodProd] &&
            'CostTable'[Fecha] = LatestDate
        )
    )

     

    (3) Then the result is as follows.

     

    Best Regards,

    Neeko Tang

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

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      1 year ago

      Hello!

      Thank you very much for the answer. This one was useful but I had to add only one detail to LatestDate, since I wanted the date to be earlier than the date of the document's output.

      MostRecentCost = 
VAR LatestDate = 
    CALCULATE(
        MAX('CostTable'[Fecha]),
        FILTER(
            'CostTable',
            'CostTable'[CodProd] = 'EventsTable'[CodProd] &&
            'CostTable'[Fecha] <= 'EventsTable'[Date]
        )
    )
RETURN
    CALCULATE(
        MAX('CostTable'[CostoUnitario]),
        FILTER(
            'CostTable',
            'CostTable'[CodProd] = 'EventsTable'[CodProd] &&
            'CostTable'[Fecha] = LatestDate
        )
    )

      With that variable in the filter, it worked perfectly for me.