Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
deb_power123
Helper V
Helper V

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

1 ACCEPTED SOLUTION

@deb_power123 

please see the attachment and check if this is what you want

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

13 REPLIES 13
ryan_mayu
Super User
Super User

@deb_power123 

you can try this

LastAttendanceDatetimestamp = max('Table'[LastAttendanceDate])

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

 

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 :-
expected.JPG
 

@deb_power123 

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

please kindly see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thankyou that was really helpful.

I have a last query, in my data i have two additional fields also added i.e location and desc which signifies the gates from which the student have swiped and entered the school and its description.

I want to also categorize these location and desc columns based on the Last attendancedate timestamp and second last attendancedate timestamp. Please suggest

 

I have added my pbix file with the datasource included.

PBIXfile

my dataource input excel:

Datasource excel

 

Expected output should be something like below:-

expect.JPG

could you please suggest how can i add the location and desc as shown above which corresponds to the last attendance dates and second last attendance dates.What changes should i make to the measures you suggested.

Please provide your suggestions and thankyou for all the inputs.It was really helpful

Kind regards

sameer

@deb_power123 

please see the attachment below

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi,

When I used the same concept in my original dataset , i could get Last attendance date location but not secondLastAttendancedate location.It showed as blank/null. Can we do something like search the second Last attended date against the Last Attended Date column and when there is a match against the location, add the location to the new column secondLastAttendeddate location? 

 

since the second location just fetched null values where as in database we have values and it is showing in the transformation tab too. Rest are all coming fine.

 

Is there any other measure we can use like lookup or so so search the match and find and update th enew column with location.

 

3.JPG

 

since we now have '1' value second Last attendance date so it will do a search in 2 Last attendance date and fetch the location Gate1 in step 3 and update it in the new second Last attendance date column. I was just thinking if such an approach is possible or any other way it can be handled?

 

Because due to some unknown reasons the second Last attendance date location is not working and it is showing no values.

Please suggest incase we can use any such measures or any other approach.As this is the point which is not coming ,rest all worked really like charm following your suggested approach.

and thankyou for all your inputs until now.It really dot me exposed to new ways in DAX in which I am little new

KInd regards

Sameer

@deb_power123 

please see the attachment and check if this is what you want

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hey @ryan_mayu @amitchandak  Thankyou for all your suggestions and yes it worked except the second location on,one of my recent thread has the solution using TOP1 but thankyou again for making me learn new concepts

Hi @ryan_mayu  I added a flag column namely 1 and 2.  Flag 1 denotes the student with lastAttendancedate and its corresponding location and desc and Flag 2 denotes the SecondLast AttendanceDate and corresponding location and desc.

 

Can we add any measure to get : lastAttendancedate ,lastAttendancedatelocation and lastAttendancedatedesc and Flag 2 denotes the SecondLast AttendanceDate and SecondLast AttendanceDatecorrespondinglocation and SecondLast AttendanceDatedesc based on the flag value column.

If it was a import table then I could have used conditional column concept and it would have worked but in ourcase we are using Direct query from SQL so conditional column transformation wont support so we need a DAX measure to evaluate it.My original dataource is SQL

 

I added my new input data source as below link:-

New datasource with flag 

 current dataset looks like this with an additional flag column

1.JPG

Could you please suggest any measure or DAX to handle this or how can we modify the above DAX which you suggested 

@deb_power123 

please see the attachment below.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thankyou this was a cool method

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

@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 )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.