latest date before the date of each row

[Anonymous]

I have a table for a production line, where each row is an individual product. One column contains the date and time where production started.

I want to calculate the time elapsed between this date, and the start of production of the last product made before (cycle time).

In other words, in a list of dates I want to identify the latest date before the date of each row. 

In Access I can do that by duplicating the table_1 to table_2, and looking for the maximum date in table_2 which is smaller than the current date in table_1.

In Power BI query I cannot figure out how to make this simple operation.

Any help will be greatly appreciated !

Regards

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column

=CALCULATE(MAX(Data[Date_Time]),FILTER(Data,Data[Date_Time]<EARLIER(Data[Date_Time])))

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column

=CALCULATE(MAX(Data[Date_Time]),FILTER(Data,Data[Date_Time]<EARLIER(Data[Date_Time])))

Hope this helps.

[Mkxxx]

Hi,

I've tried this solution but there goes an error (circular reference)

Could you please help?

[Ashish_Mathur · Accepted solution]

Hi,

My formula has to be written as a calculated column - not as a measure.

[Anonymous]

yes, in this way I tried back in March, and it worked !

thank you again

[Ashish_Mathur]

You are welcome.

[Mkxxx]

Hi, I tried this solution and but it's not working ( a circular reference)

Could you help please?

[Anonymous]

Seems good at 1st test. Thanks !

[Ashish_Mathur]

You are welcome.