finding the last record per day, per customer

[shanwise101]

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:

Customer	timestamp	Qty
Customer A	4/04/2020 11:07	0
Customer A	4/04/2020 16:24	1
Customer A	4/04/2020 20:03	1
Customer A	5/04/2020 8:17	2
Customer A	5/04/2020 10:53	2
Customer A	5/04/2020 12:29	2
Customer A	5/04/2020 13:18	2
Customer A	5/04/2020 16:53	2
Customer A	5/04/2020 19:59	3
Customer A	6/04/2020 8:51	3
Customer A	6/04/2020 9:56	1

Result (with additional customer for context)

Customer	timestamp	Qty
Customer A	4/04/2020 20:03	1
Customer A	5/04/2020 19:59	3
Customer A	6/04/2020 9:56	1
Customer B	6/04/2020 20:02	2
Customer B	7/04/2020 7:55	2

Any guidance would be much appreciated, many thanks all.

[mahoneypat · Accepted solution]

Here is one way to get your desired result as a DAX calculated table

1. Add a Date column to your table (I called your example data table "Qty") with this - 

Date = FORMAT(Qty[timestamp], "MM/DD/YYYY")

 

2. Make a DAX table with this expression

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 could also use the above in a measure variable and do further analysis on it, if you prefer a measure.

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

Regards,

Pat

[camargos88 · Accepted solution]

Hi @shanwise101 ,

Check this code for a calculated column:

Max_Timestamp = 
VAR _date = 'Table'[timestamp]
VAR _customer = 'Table'[Customer]
VAR _Qty = 'Table'[Qty]
RETURN
IF (_date = 
CALCULATE(
            MAX('Table'[timestamp]), 
            FILTER('Table', 
                    'Table'[Customer] = _customer && 
                    'Table'[Qty] = _Qty &&
                    DATE(YEAR('Table'[timestamp]), MONTH('Table'[timestamp]), DAY('Table'[timestamp])) = DATE(YEAR(_date), MONTH(_date), DAY(_date))
            )
), 1, 0)

[v-eachen-msft · Accepted solution]

Hi @shanwise101 ,

You could also use Power Query to do it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldA7CoAwDAbgq5TOhSbpS7OJxygdHUXwcX8rQtFF24zJlxD+GOV4bPsyT6sYRCmppNVgNQGBQGQIrxnIpGoWPZPNHazjBAzmj7vCO8aQG1SnEdiZBk5MfQM3jF0D923P9OyuZ8wX949gHNbrfNvfoacT", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, timestamp = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"timestamp", type datetime}, {"Qty", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.From([timestamp])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Customer", "Custom"}, {{"Max", each _, type table [Customer=text, timestamp=datetime, Qty=number, Custom=date]}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Max"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "MaxDate", each List.Max([Max][timestamp])),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom1", "Max", {"Customer", "Qty", "timestamp"}, {"Max.Customer", "Max.Qty", "Max.timestamp"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Count", each ([Max.timestamp] = [MaxDate])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"MaxDate"})
in
    #"Removed Columns"

Here is the result.

Here is the test file for your reference.

[camargos88 · Accepted solution]

Hi @shanwise101 ,

Check this code for a calculated column:

Max_Timestamp = 
VAR _date = 'Table'[timestamp]
VAR _customer = 'Table'[Customer]
VAR _Qty = 'Table'[Qty]
RETURN
IF (_date = 
CALCULATE(
            MAX('Table'[timestamp]), 
            FILTER('Table', 
                    'Table'[Customer] = _customer && 
                    'Table'[Qty] = _Qty &&
                    DATE(YEAR('Table'[timestamp]), MONTH('Table'[timestamp]), DAY('Table'[timestamp])) = DATE(YEAR(_date), MONTH(_date), DAY(_date))
            )
), 1, 0)

[mahoneypat · Accepted solution]

Here is one way to get your desired result as a DAX calculated table

1. Add a Date column to your table (I called your example data table "Qty") with this - 

Date = FORMAT(Qty[timestamp], "MM/DD/YYYY")

 

2. Make a DAX table with this expression

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 could also use the above in a measure variable and do further analysis on it, if you prefer a measure.

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

Regards,

Pat