cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leschi
Frequent Visitor

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
1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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

View solution in original post

2 REPLIES 2
Jimmy801
Community Champion
Community Champion

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

View solution in original post

leschi
Frequent Visitor

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

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors