cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Find the last trasaction per day, per customer

Hello All,

I have a dataset that captures multiple customers who update a quantity field several times a day, differentiated by a timestamp.

I need to be able to display by way of table or column or measure:

  • the last record per day
  • per customer
  • and the associated quantity.

Dataset:

CustomertimestampQty
Customer A          4/04/2020 11:07          0
Customer A          4/04/2020 16:241
Customer A          4/04/2020 20:031
Customer A          5/04/2020 8:172
Customer A          5/04/2020 10:532
Customer A          5/04/2020 12:292
Customer A          5/04/2020 13:182
Customer A          5/04/2020 16:532
Customer A5/04/2020 19:593
Customer A          6/04/2020 8:513
Customer A          6/04/2020 9:561

 

Result (with additional customer for context)

CustomertimestampQty
Customer A          4/04/2020 20:03          1
Customer A5/04/2020 19:593
Customer A6/04/2020 9:561
Customer B6/04/2020 20:022
Customer B7/04/2020 7:552

 

Any guidance would be much appreciated, many thanks all.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: Find the last trasaction per day, per customer

Is this a duplicate post?  In any case, this DAX expression for a calculated table will return your result from your example data.

 

Latest =
ADDCOLUMNS (
    SUMMARIZE ( Qty, Qty[Customer], Qty[Date] ),
    "@time", CALCULATE ( MAX ( Qty[timestamp] ) ),
    "@Qty",
    VAR maxtime =
        CALCULATE ( MAX ( Qty[timestamp] ) )
    RETURN
        CALCULATE ( AVERAGE ( Qty[Qty] ), Qty[timestamp] = maxtime )
)

 

You can adapt it for use in a measure too, if needed.  You can store the above table in a variable, and then analyze it within the same measure.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Highlighted
Super User V
Super User V

Re: Find the last trasaction per day, per customer

Hi,

You may download my solution workbook from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Highlighted
Super User VI
Super User VI

Re: Find the last trasaction per day, per customer

Is this a duplicate post?  In any case, this DAX expression for a calculated table will return your result from your example data.

 

Latest =
ADDCOLUMNS (
    SUMMARIZE ( Qty, Qty[Customer], Qty[Date] ),
    "@time", CALCULATE ( MAX ( Qty[timestamp] ) ),
    "@Qty",
    VAR maxtime =
        CALCULATE ( MAX ( Qty[timestamp] ) )
    RETURN
        CALCULATE ( AVERAGE ( Qty[Qty] ), Qty[timestamp] = maxtime )
)

 

You can adapt it for use in a measure too, if needed.  You can store the above table in a variable, and then analyze it within the same measure.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Highlighted
Super User V
Super User V

Re: Find the last trasaction per day, per customer

Hi,

You may download my solution workbook from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors