Show date when data changes occured

[florinaturlea]

Hi!

I am tracking a list of tickets (Ticket column) over time (Date column) for which I have a status (Done or In Progress) and a ticket date - when the status has been set (Ticket Date).

Date	Ticket	Status	Ticket Date
01.10.2023	A	Done	25.09.2023
02.10.2023	A	Done	25.09.2023
03.10.2023	A	In Progress	27.10.2023
04.10.2023	A	In Progress	27.10.2023
05.10.2023	A	In Progress	30.10.2023
01.10.2023	B	In Progress	25.09.2023
02.10.2023	B	Done	26.09.2023
03.10.2023	B	In Progress	30.11.2023
04.10.2023	B	In Progress	30.11.2023
05.10.2023	B	In Progress	29.11.2023

I want to show:

1. The date when the status changed, in this case would be:

For ticket A - 03.10.2023 (status changed from Done to In Progress)

For ticket B, since there are 2 changes, I need the most recent one, 03.10.2023. 

2. The date when the ticket date changed, in this case would be: 

For ticket A - 05.10.2023 (ticket date changed from 27.10 to 30.10 and status remained the same)

For ticket B - 05.10.2023 (ticket date changed from 30.11 to 29.11 and status remained the same).

I have tried different formulas, but I dont seem to get it right as I need it in a table in frontend, not in the load script.

Any idea?

Thanks!

[PrashantSangle]

hi,

Can you draft your final output? 

Regards,

Prashant Sangle

[Mark_Little]

Hi,

I would be looking at making a sperate table (Maybe join back in at the end), But you will be looking at using PEEK and Previous functions. Then use aggregation using group by to pick the max dates.

[florinaturlea]

Hi,

The changes should be tracked on Ticket level:

Date	Ticket	Status	Status Change	Ticket Date	Date Change
01.10.2023	A	Done	03.10.2023	25.09.2023	05.10.2023
02.10.2023	A	Done	03.10.2023	25.09.2023	05.10.2023
03.10.2023	A	In Progress	03.10.2023	27.10.2023	05.10.2023
04.10.2023	A	In Progress	03.10.2023	27.10.2023	05.10.2023
05.10.2023	A	In Progress	03.10.2023	30.10.2023	05.10.2023
01.10.2023	B	In Progress	03.10.2023	25.09.2023	05.10.2023
02.10.2023	B	Done	03.10.2023	26.09.2023	05.10.2023
03.10.2023	B	In Progress	03.10.2023	30.11.2023	05.10.2023
04.10.2023	B	In Progress	03.10.2023	30.11.2023	05.10.2023
05.10.2023	B	In Progress	03.10.2023	29.11.2023	05.10.2023

So, I need to create 2 additional columns: Status Change and Date Change. Status change shows the date when the status changed (for A is 03.10 because it changed from Done to In Progress and for B is also 03.10 because it changed from Done to In Progress) and Date Change is the date when Ticket Date changed (status remained the same)