Getting the previous date before the most recent

[Anonymous]

Good afternoon,

I would like help extracting the date before the last date registered in a dataset. For instance, i have similar services being realized and i would like to know how long did it take to return into maintenance, which means, date of sending - compared to the date of return. 

The following figure will describe what i want: to see the previous date of sending (data envio) before the actual date of sending( last sending date) and compare it with returning date to get the time between repairs.

[Greg_Deckler · Accepted solution]

First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Second, See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[v-easonf-msft]

Hi , @Anonymous 

Not very clear .Can you show me more details?

Sample data/Pbix file  and expected result  will make it easier for us to understand and  solve your problem.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Good morning.

Thanks for the fast answer. i will send you a sample for testing.

What i want: do show the previous date of sending to maintenance of my dataset ( dt-envio) in order to know how long did it took to show the same defect. 

cod-cilindro	LAST DATE(ultima data)	SENDING DATE (dt-envio)	desc-motivos	desc-servicos
609332	24/10/2019 00:00	24/10/2019 00:00	Batida/Risco
609332	09/11/2019 00:00	09/11/2019 00:00	Conferência Da Arte / Registro	Serviço Não Executado
609332	12/11/2019 00:00	12/11/2019 00:00	Conferência Da Arte / Registro	Bater Prova
609332	27/12/2019 00:00	27/12/2019 00:00	Desgaste	Recromo
609332	11/01/2020 00:00	11/01/2020 00:00	Desgaste	Gravação
609332	19/02/2020 00:00	19/02/2020 00:00	Desgaste	Gravação

the red one should be substituted by the purple one

I can't upload data into the forum. 

[v-easonf-msft · Accepted solution]

Hi,  @Anonymous 

Try steps as below:

1.create index column "Index" in query Editor and  click "close&apply"

2.Sort ascending by index, and create calculated column " Last Date2" as below:

(here ,you need to pay attention  to the Data type of your calculate column )

Last Date2 =
VAR a =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        'Table',
        'Table'[cod-cilindro] = EARLIER ( 'Table'[cod-cilindro] ),
        'Table'[Index] < EARLIER ( 'Table'[Index] )
    ) + 0
RETURN
    IF (
        'Table'[Index2] = 0,
        BLANK (),
        CALCULATE (
            SUM ( 'Table'[LAST DATE(ultima data)] ),
            'Table',
            'Table'[Index] = a
        )
    )

Here is a demo.

Pbix Attached 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler · Accepted solution]

First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Second, See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...