Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Create a table with latest transaction

Hi everyone,

I'm quite new to PowerBI and struggling with something.

 

I have a table with:

PurchaseOrderNumber

ItemNumber

Date of creation

 

This table has all POs ever made for that item number.

 

I need to have a table with only the latest transaction of a certain item. Something like this:

 

PurchaseOrderNumberItemDate created PurchaseOrderNumberItemDate created
1A1/1/2020 1A1/1/2020
1C1/1/2020 2B1/2/2020
1E1/1/2020 1C1/1/2020
1F1/1/2020 4D1/3/2020
2A1/2/2020 1E1/1/2020
2B1/2/2020 1F1/1/2020
2E1/2/2020    
2F1/2/2020    
3A1/2/2020    
3B1/2/2020    
3C1/2/2020    
4A1/3/2020    
4B1/3/2020    
4C1/3/2020    
4D1/3/2020    

 

The right table would be my result.

 

Thank you!

Need Help

5 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    Hi Anonymous - I answered one that I think is almost exactly like this yesterday, and included a PBIX file to play with. Take a look and see if that answers your question.

    If not, let me know what the diffference is in the scenarios and we will try to help you tweak those DAX measures here to fit your situation.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      I tried based on that solution, but couldn't make it work 😪

      In my case I only have one table with all data, and I just want to create a new table (not measure) showing only the latest transactions of that list.

      I tried SUMMARIZE with filter, but couldn't make it work. Also tried SELECTEDCOLUMNS and nothing.

       

       

      • edhans's avatar
        edhans
        Community Champion
        6 years ago

        Ok. The following creates a table:

         

        Summarized Table = 
SUMMARIZECOLUMNS(
    'Purchase Data'[Item],
    "PO ",MAX('Purchase Data'[PurchaseOrderNumber]),
    "Date", LASTDATE('Purchase Data'[Date created])
)

        But the results seem to be what you explained, but it isn't the same as your sample table. 

         

        In other words, you said "I need to have a table with only the latest transaction of a certain item. Something like this" but then for item A you picked PO #1 on Jan 1, 2020, but item A was purchased on PO #4 on Jan 3, which is the latest transaction.

        If this isn't right, can you please clarify?