Want most recent record for each day (date) for multiple projects

[erieqlik]

I have a history table with the following: project name, status, create date (of status), and a bunch of other fields.

I want the most recent record for each project name & project type.

Data:

Load * inline [

project name, status, create date

Alpha, Backlog, 2/16/2024 1:41:31 PM

Alpha, Approved, 2/16/2024 1:45:13 PM

Alpha, In Progress, 2/16/2024 1:57:45 PM

Alpha, Completed, 2/19/2024 3:45:33 PM

Beta, Backlog, 2/1/2024 9:01:01 AM

Beta, In Progress, 2/1/2024 4:01:54 PM

Beta, Backlog, 2/7/2024 8:02:12 AM

Beta, Approved, 2/12/2024 12:10:17 PM

Beta, Completed, 2/12/2024 2:44:59 PM

];

My result table should show just these data points…

Alpha, In Progress, 2/16/2024 1:57:45 PM <- this was the last thing that happened on 2/16

Alpha, Completed, 2/19/2024 3:45:33 PM <- this was the only thing that happened on 2/19

Beta, In Progress, 2/1/2024 4:01:54 PM <- this was the last thing that happened on 2/1

Beta, Backlog, 2/7/2024 8:02:12 AM <- this was the only thing that happened on 2/7

Beta, Completed, 2/12/2024 2:44:59 PM <- this was the last thing that happened on 2/12

My actual data has many more columns – how would I preserve that data?

[igoralcantara]

Try this:

Data:

Load * inline [

project name, status, create date

Alpha, Backlog, 2/16/2024 1:41:31 PM

Alpha, Approved, 2/16/2024 1:45:13 PM

Alpha, In Progress, 2/16/2024 1:57:45 PM

Alpha, Completed, 2/19/2024 3:45:33 PM

Beta, Backlog, 2/1/2024 9:01:01 AM

Beta, In Progress, 2/1/2024 4:01:54 PM

Beta, Backlog, 2/7/2024 8:02:12 AM

Beta, Approved, 2/12/2024 12:10:17 PM

Beta, Completed, 2/12/2024 2:44:59 PM

];

Summary:

Load

    [project name],

    status,

    Timestamp(Min([create date])) As [First Occurance]

Resident Data

Group By [project name], status;

[erieqlik]

This is closer but not there yet. A story/project can go through a series of events (status changes) in a day. I want the last thing that happened to a project on any given day. If status changes 3 times in a day:

backlog to review

review to in progress

in progress to completed

The above would have a single record for the last status of completed.

[igoralcantara]

So, you want for each status in a project the last update in each date or the last status update for a record?

[igoralcantara]

Let's try this then:

Data:
Load * inline [
project name, status, create date
Alpha, Backlog, 2/16/2024 1:41:31 PM
Alpha, Approved, 2/16/2024 1:45:13 PM
Alpha, In Progress, 2/16/2024 1:57:45 PM
Alpha, Completed, 2/19/2024 3:45:33 PM
Beta, Backlog, 2/1/2024 9:01:01 AM
Beta, In Progress, 2/1/2024 4:01:54 PM
Beta, Backlog, 2/7/2024 8:02:12 AM
Beta, Approved, 2/12/2024 12:10:17 PM
Beta, Completed, 2/12/2024 2:44:59 PM
];

Summary:
Load
[project name],
Timestamp(Max([create date])) As [Last Status Change]
Resident Data
Group By [project name], status;

Left Join(Summary)
Load
[project name],
[create date] As [Last Status Change],
status As [Last Status]
Resident Data;

[erieqlik]

I just want these records from data:

[igoralcantara]

Cool. Let's try this now:

Data:
Load *
, Date(Floor([create date])) As CreateDateOnly
;
Load * inline [
project name, status, create date
Alpha, Backlog, 2/16/2024 1:41:31 PM
Alpha, Approved, 2/16/2024 1:45:13 PM
Alpha, In Progress, 2/16/2024 1:57:45 PM
Alpha, Completed, 2/19/2024 3:45:33 PM
Beta, Backlog, 2/1/2024 9:01:01 AM
Beta, In Progress, 2/1/2024 4:01:54 PM
Beta, Backlog, 2/7/2024 8:02:12 AM
Beta, Approved, 2/12/2024 12:10:17 PM
Beta, Completed, 2/12/2024 2:44:59 PM
];

Summary:
Load
[project name],
CreateDateOnly,
Timestamp(Max([create date])) As [Last Status Change]
Resident Data
Group By [project name], CreateDateOnly;

Left Join(Summary)
Load
[project name],
CreateDateOnly,
[create date] As [Last Status Change],
status As [Last Status]
Resident Data;

Droop Field CreateDateOnly;

[erieqlik]

I think I get it. I need to select and then join my results. I just need to determine how.

[igoralcantara · Accepted solution]

Check my last code. I think this is it.

[erieqlik]

I should have asked... What does this mean/do? How does it work? It appears to be part of the 'Data' table but I do not see it in the data model viewer.