[SQL] Add calculated field based on past values of the row SQL

I have a data set with a date field and column 'X' as shown below. X is a binary field - I want to add a field at the end ('desired field (calculated)') that will give me the first date of where the string of 1's starts for field 'X'. Any ideas? In Excel, I would look at the row before to check if it was blank or not based on the below:

Logic:

Case when X=0 then Null
when X=1 and 'desired field value for the row above' = Null, then Date
when X=1 and 'desired field value for the row above' != Null, then value of 'desired field value for the row above'
) end as desired field​

Date (dd/mm/yy)|X|desired field (calculated)
01/01/00 | |
02/01/00 |1|02/01/00
04/01/00 |1|02/01/00
10/01/00 |1|02/01/00
20/01/00 | |
20/02/00 | |
20/03/00 | |
04/04/00 |1|04/04/00
06/04/00 |1|04/04/00
10/04/00 |1|04/04/00
20/05/00 | |

Continue reading...