Forum Discussion
Getting the previous date before the most recent
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.
Community SupportHi , 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
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
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.