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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors