How do get last Date from a SalesLine Table

[leschi]

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:

ProductNumber	ProductName	Date
123	Pencil	09.12.2019
543677	PC	16.12.2019
4567	Keyboard	05.12.2019
123	Pencil	05.12.2019
543677	PC	15.12.2019
543677	PC	14.12.2019
4567	Keyboard	12.12.2019
123	Pencil	01.12.2019
123	Pencil	02.12.2019

[Jimmy801 · Accepted solution]

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]

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