Need to find the second last value

[Rinosh]

Hi all,

Need your valuable help.

Need  to find out the second last value ( date & value) from the  data table

Example : from the below table ,sould find out the below values

1. Latest date  & Kms/miles , if maintanace type is PMS  ( solved using Maxx & filter)

2. Second last date & Kms/miles , if maintanance type is PMS ( Need help to find out)

3.  Diffrence between both dates & Kms/Miles. ( Solved using diffrence)

The result expected is  , for the vehicle KTU 920 ER ( Should vary based on vehicles)

1. latest service date  & KMS =10/12/2022 & 266000 Kms

2. Second last service date & Kms = 04/12/2022 & 265432 Kms

Date	Vehicle			Category	Kms/Miles	Maintanance type
3/12/2022	KTU 920 ER			LMV	265432	RR
4/12/2022	KTU 920 ER			LMV	265432	PMS
5/12/2022	KTU 920 ER			LMV	265432	RR
10/12/2022	KTU 920 ER			LMV	266000	PMS

[Greg_Deckler]

@Rinosh Try:

Second To Last Date Measure =
  VAR __Last = MAXX(ALL('Table'),[Date])
  VAR __SecondToLast = MAXX(FILTER(ALL('Table'),[Date]<__Last),[Date])
RETURN
  __SecondToLast


Second To Last Value Measure =
  MAXX(FILTER(ALL('Table'),[Date] = [Second To Lst Date Measure]),[Kms/Miles])

[Rinosh]

Hi, thank you for your quick responce.

I am getting error on the above dax function.  Also what i was expecting , Second last date & Kms  if vehicle maintance type is PMS

[Padycosmos]

For "PMS" , the dates in dEscending order are 15/12/22,13/12/22,04/12/22 and 03/12/22

The corresponding second last dates are : 13/12/22,04/12/22,03/12/122,No date

I hope the following measure helps:

Second_Last_Date =

VAR Last_date= calculate(LASTDATE('Table'[Date]),'Table'[Job] = "PMS")

VAR SecondLastDate = MAXX(FILTER(ALL('Table'),'Table'[Date]<Last_date && 'Table'[Job] = "PMS"),[Date])

RETURN

SecondLastDate

Here is the result

[Greg_Deckler]

@Rinosh Had a minor typo in the name of the measure. Attached a PBIX below signature, here are the updated measures:

Second To Last Date Measure PMS = 
  VAR __Last = MAXX(FILTER(ALL('Table'), [Maintanance type] = "PMS"),[Date])
  VAR __SecondToLast = MAXX(FILTER(ALL('Table'),[Date]<__Last && [Maintanance type] = "PMS"),[Date])
RETURN
  __SecondToLast



Second To Last Value Measure PMS = 
  MAXX(FILTER(ALL('Table'),[Date] = [Second To Last Date Measure PMS] && [Maintanance type] = "PMS"),[Kms/Miles])

[Rinosh]

@Greg_Deckler 

Really thank you for your effort.

The function is working partially for my requirment.

if the data sets are with multiple vehicle number , its getting considered  overall second last date & value

Date	Registration or Serial No.	Nature of Job	Kms/Miles
12/4/2022	KTU 920 ER	PMS	265432
12/5/2022	KTU 920 ER	RR	265432
12/12/2022	KTU 920 ER	RR	265432
12/15/2022	KTU 920 ER	PMS	265666
12/29/2022	KTU 920 ER	RR	266376
1/6/2023	KTU 920 ER	RR	266882
12/3/2022	EPE 572 ER	PMS	256359
12/13/2022	EPE 572 ER	PMS	257245
12/28/2022	EPE 572 ER	RR	258091
1/5/2022	EPE 572 ER	RR	258752

For above data set, 

 i am getting the result of 

Vehicle number	Second To Last Date	Second To Last Value Measure
EPE 572 ER	12/13/2022	257245
KTU 920 ER	12/13/2022	257245

Expected result is 

Vehicle number	Second To Last Date	Second To Last Value Measure
EPE 572 ER	12/3/2022	256359
KTU 920 ER	12/4/2022	265432

Can you please help to adress this issue also.

Really thank you for the help.

[Ahmedx]

I think you are confused, the result should be
or something I don't understand

[Rinosh]

Appolagise the mistake,

Resuld should be,

Second last date & value of maintanance type "PMS" 

The under lined  line  will be the second last value of maintanace type "PMS"

Date	Registration or Serial No.	Nature of Job	Kms/Miles
12/4/2022	KTU 920 ER	PMS	265432
12/5/2022	KTU 920 ER	RR	265432
12/12/2022	KTU 920 ER	RR	265432
12/15/2022	KTU 920 ER	PMS	265666
12/29/2022	KTU 920 ER	RR	266376
1/6/2023	KTU 920 ER	RR	266882
12/3/2022	EPE 572 ER	PMS	256359
12/13/2022	EPE 572 ER	PMS	257245
12/28/2022	EPE 572 ER	RR	258091
1/5/2022	EPE 572 ER	RR	258752

[Greg_Deckler · Accepted solution]

@Rinosh If you need it to consider vehicle number then it is just:

Second To Last Date Measure PMS = 
  VAR __Vehicle = MAX('Table'[Registration or Serial No.])
  VAR __Last = 
    MAXX(
      FILTER(
        ALL('Table'), 
        [Registration or Serial No.] = __Vehicle && [Maintanance type] = "PMS"
      ),
      [Date]
    )
  VAR __SecondToLast = 
    MAXX(
      FILTER(
        ALL('Table'),
        [Registration or Serial No.] = __Vehicle && 
          [Date]<__Last && [Maintanance type] = "PMS"
      ),
      [Date]
    )
RETURN
  __SecondToLast



Second To Last Value Measure PMS = 
  VAR __Vehicle = MAX('Table'[Registration or Serial No.])
  VAR __Result = 
    MAXX(
      FILTER(
        ALL('Table'),
        [Registration or Serial No.] = __Vehicle && 
          [Date] = [Second To Last Date Measure PMS] && [Maintanance type] = "PMS"
      ),
      [Kms/Miles]
    )
RETURN
  __Result

[Ahmedx]

look is this what you need?

https://dropmefiles.com/Nv1Mo

Second Max Date = 
VAR _tbl =
    CALCULATETABLE ( TOPN ( 2, 'Table', 'Table'[Date], DESC ) )
VAR _RESULT =
    CALCULATE ( MIN ( 'Table'[Date] ), _tbl )
RETURN
    IF ( ISINSCOPE ( 'Table'[Registration or Serial No.] ), _RESULT )
------
total = 
SUM ( 'Table'[Kms/Miles] )
----
Second Max KM = 
SUMX (
    VALUES ( 'Table'[Registration or Serial No.] ),
    VAR _second = [Second Max Date]
    RETURN
        CALCULATE ( [total], 'Table'[Date] = _second )
)

[Rinosh]

@Ahmedx 

Thank you for the support.

Not getting right  result  as expected due to  vehicle maintance type = PMS  not considered in the function.

Result recieved is 

Vehicle number	Second max date	Second max Kms
KTU 920 ER	12/29/2022	266376
EPE 572 ER	12/13/2022	257245

Expected result is 

Vehicle number	Second To Last Date	Second To Last Value Measure
EPE 572 ER	12/3/2022	256359
KTU 920 ER	12/4/2022	265432