Require your help in finding out time difference between two dates in the same column.
I want to know how much is the time gap between the receipts cut by a person (Objective: It is to find an anomaly between receipts cut by a person – less than one minute).
I have attached the sample data.
In excel, I used to follow below steps to find the time gap between the receipts cut by a person. I want to replicate the same using DAX measures. Is it possible to do the same without any additional columns in power pivot data model containing million rows of data (100 MB).
First, do multiple sorting by Column collected by and then by column Collection DTM.
Second create new column (TimeSpan) and apply new formula.
I wanted to replicate the same steps in power pivot. I know the step of doing it in power query (indexing and doing vlookup) but dont want to follow that method as my data size is 32 lakhs of data. It will block the memory.
So interested in doing it in DAX measures without creating any additonal columns in power pivot data model.
Please find attatched the sample data for your perusal
Additional information, I have used rankx function to sort the data..
Rankx function to sort the data in ascending order
Then created a Time Span less than one minute column using below code.
Time Span less than one minute calculated column
This additional columns increases the file size. So is there any dax measures which can do do both sorting and subract current row from previous row. I heard that it is better to use dax measures than calculated column when you have one million rows of data.