Forum Discussion

deb_power123's avatar
deb_power123
Helper V
5 years ago
Solved

DateTime intelligence DAX function to find the Last and second last dates

Hi All,

I have a student table with the following columns:-

  1. StudentID
  2. StudentName
  3. LastAttendanceDate
  4. StudentType
  5. enteringGate

The column Last Attendance date has the date time stamp of the student swiped into the building from various gate entry points of the school.Now I want to seggregate the datetime stamp column i.e :Last AttendanceDate into the below column categories:-

1. LastAttendanceDatetimestamp
2. SecondLandAttendanceDatetimestamp

so which ever is the last attendance datetimestamp should be shown in LastAttendanceDatetimestamp and whichever is the second last datetimestamp should be shown inSecondLandAttendanceDatetimestamp.

My input data is as below :-

StudentIDStudentNameLastAttendanceDateStudentTypeenteringGate
100Mary02-05-2011 10:45FulltimeGate1
100Mary02-05-2011 12:45FulltimeGate3
100Mary02-05-2011 09:45FulltimeGate4
101John02-05-2011 11:59Part TimeGate1
101John02-05-2011 12:56Part TimeGate3
101John02-05-2011 09:55Part TimeGate3
101John02-05-2011 10:48Part TimeGate2

 

Expected Output:

StudentIDStudentNameLastAttendanceDatetimestampSecondLastAttendanceDatetimestampStudentType 
100Mary02-05-2011 12:4502-05-2011 10:45Fulltime 
101John02-05-2011 12:5602-05-2011 11:59Part Time 

 

Is it possible to write any DAX to handle this scenario on a runtime? There are more records so it needs to evaluate the above condition and categorize into Last attendancedatetimestamp and second last attendancedate timestamp custom columns.

 

Please suggest any possibile DAX to handle this?

 

Kind regards

Sameer

Need Help

13 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    deb_power123 

    you can try this

    LastAttendanceDatetimestamp = max('Table'[LastAttendanceDate])

SecondLast = MAXX(FILTER('Table','Table'[LastAttendanceDate]<max('Table'[LastAttendanceDate])),'Table'[LastAttendanceDate])

     

    • deb_power123's avatar
      deb_power123
      Helper V
      5 years ago

      Hi ryan_mayu  so incase I have to show the third last attendance date timestamp as well .Can i modify your measure like this?

       

      ThirdLast = MAXX(FILTER('Timing','Timing'[LastAttendanceDate]<max('Timing'[SecondLast])),'Timing'[SecondLast])
       
      I tired to use this but it threw error since we are calculating the secondlast as a measure which is calculated on run time and the formula looks for column.
       
      Could you please suggest how can I modify or handle the scenario where I need to show the third last date as well. My input excel data link is as below :-
       
       
       
       
      Please find below the expected output :-
       
      • ryan_mayu's avatar
        ryan_mayu
        Super User
        5 years ago

        deb_power123 

        ThirdLastAttendanceDatetimestamp = MAXX(FILTER(students,[LastAttendanceDatetimestamp]<MAXX(FILTER(students,'students'[LastAttendanceDate]<max('students'[LastAttendanceDate])),'students'[LastAttendanceDate])),[LastAttendanceDatetimestamp])

        please kindly see the attachment below

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    deb_power123 , Try this measure

     

    Measure =
    VAR __id = MAX ('Table'[StudentID] )
    VAR __date = CALCULATE ( MAX('Table'[LastAttendanceDate] ), ALLSELECTED ('Table' ), 'Table'[StudentID] = __id )
    CALCULATE ( MAX ('Table'[LastAttendanceDate] ), VALUES ('Table'[StudentID] ),'Table'[StudentID] = __id,'Table'[LastAttendanceDate] < __date )