Forum Discussion

leschi's avatar
leschi
Frequent Visitor
6 years ago
Solved

How do get last Date from a SalesLine Table

Hello,

i want to create a Table where i can see all my Products with their last sale date. Also the Daterange should be choosable.

I tried it and it is not possible with a standard date-range filter.

 

So my second idea is to create a nother Table with the product number and the last sale date.

Does anyone have an idea how to to this?

 

I hope you have some ideas or tips how to solve this problem.

 

 

Here is some sample data from the salesline table:

ProductNumberProductNameDate
123Pencil09.12.2019
543677PC16.12.2019
4567Keyboard05.12.2019
123Pencil05.12.2019
543677PC15.12.2019
543677PC14.12.2019
4567Keyboard12.12.2019
123Pencil01.12.2019
123Pencil02.12.2019
Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello leschi ,

     

    you can apply some simple grouping. But this will not fit your needs as you can't choose the daterange.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRCkjNS87MATIMLPUMjfSMDAwtlWJ1opVMTYzNzM1BCpyBhKEZiqSJqRlIyju1Mik/sSgFpNsURQG62ab4zMYraULAYqAsPosN8coi6Y0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProductNumber = _t, ProductName = _t, Date = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"ProductNumber", Int64.Type}, {"ProductName", type text}, {"Date", type date}}),
    GroupedRows = Table.Group
    (
        ChangeType, 
        {"ProductNumber", "ProductName"},
        {{"MaxDate", each List.Max([Date]), type date}}
    )
in
    GroupedRows

     

    In Power BI is no way to include a external parameter in Power Query. This could be done in Excel. Using Power BI the only way would be to use DAX

     

    If this post helps or solves your problem, please mark it as solution.
    Kudos are nice to - thanks
    Have fun

    Jimmy

2 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello leschi ,

     

    you can apply some simple grouping. But this will not fit your needs as you can't choose the daterange.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRCkjNS87MATIMLPUMjfSMDAwtlWJ1opVMTYzNzM1BCpyBhKEZiqSJqRlIyju1Mik/sSgFpNsURQG62ab4zMYraULAYqAsPosN8coi6Y0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProductNumber = _t, ProductName = _t, Date = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"ProductNumber", Int64.Type}, {"ProductName", type text}, {"Date", type date}}),
    GroupedRows = Table.Group
    (
        ChangeType, 
        {"ProductNumber", "ProductName"},
        {{"MaxDate", each List.Max([Date]), type date}}
    )
in
    GroupedRows

     

    In Power BI is no way to include a external parameter in Power Query. This could be done in Excel. Using Power BI the only way would be to use DAX

     

    If this post helps or solves your problem, please mark it as solution.
    Kudos are nice to - thanks
    Have fun

    Jimmy

    • leschi's avatar
      leschi
      Frequent Visitor
      6 years ago

      Thanks for your help !!
      with some modifications it worked like a charm 😉