Forum Discussion

Kumshan450's avatar
Kumshan450
Icon for Helper III rankHelper III
7 years ago

Dax measure to calculate time difference between two data/time values

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 an...
Need Help
Kumshan450's avatar
Kumshan450
Icon for Helper III rankHelper III
7 years ago

Sorry i am accepting solution on subtracting current row from previous row using dax measures technique not calculated column. 

 

I am able to crack the solution by creating calculated column. However already mentioned data size is huge. Hence interest in using dax measures. 

 

I am able to create calculated column. below is the code. Please replicate the above code using dax measure not calculated column. 

 

TS<1min = if(CALCULATE(max(Collectiondata[COLLECTED BY]),filter(Collectiondata,Collectiondata[Index]=earlier(Collectiondata[Index])-1))<>Collectiondata[COLLECTED BY],0,if(DATEDIFF(CALCULATE(max(Collectiondata[COLLECTION DTM]),filter(Collectiondata,Collectiondata[Index]=EARLIER(Collectiondata[Index])-1)),Collectiondata[COLLECTION DTM],second)<60,1,0))
 
Sample data file link
 
Kumshan450's avatar
Kumshan450
Icon for Helper III rankHelper III
7 years ago

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.

 

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Kumshan450,

     

    DAX row context statement like earlier could not be used directly in calculate column, however, you may modify your calculate column to change to measure with formula below:

     

    Measure = 
IF (
    CALCULATE (
        MAX ( Collectiondata[COLLECTED BY] ),
        FILTER (
            ALL ( Collectiondata ),
            COUNTROWS (
                FILTER (
                    Collectiondata,
                    Collectiondata[Index]
                        = EARLIER ( Collectiondata[Index] ) - 1
                )
            )
        )
    )
        <> MAX ( Collectiondata[COLLECTED BY] ),
    0,
    IF (
        DATEDIFF (
            CALCULATE (
                MAX ( Collectiondata[COLLECTION DTM] ),
                FILTER (
                    ALL ( Collectiondata ),
                    COUNTROWS (
                        FILTER (
                            Collectiondata,
                            Collectiondata[Index]
                                = EARLIER ( Collectiondata[Index] ) - 1
                        )
                    )
                )
            ),
            MAX ( Collectiondata[COLLECTION DTM] ),
            SECOND
        )
            < 60,
        1,
        0
    )
)

    Regards,

    Jimmy Tao

    • Kumshan450's avatar
      Kumshan450
      Icon for Helper III rankHelper III
      7 years ago

      Hi Thank you very much for providing the code.

       

      I have put your code (measure name: TS) and compared the result with the calculated column result (TS<1min). Its not matching. 

       

      Please find here the screenshot. 

       

      I have send the file data link. You can refer my previous post.