Forum Discussion
Getting the previous date before the most recent
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.
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/339586
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.
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.
4 Replies
- Greg_Deckler
Community Champion
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/339586
- v-easonf-msft
Community Support
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 _ EasonGood 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
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.
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.