topic Re: Dax measure to calculate time difference between two data/time values in Desktop
https://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/587098#M278296
<P>Sorry i am accepting solution on subtracting current row from previous row using dax measures technique not calculated column. </P><P> </P><P>I am able to crack the solution by creating calculated column. However already mentioned data size is huge. Hence interest in using dax measures. </P><P> </P><P>I am able to create calculated column. below is the code. Please replicate the above code using dax measure not calculated column. </P><P> </P><DIV><DIV><SPAN>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))</SPAN></DIV></DIV>Mon, 17 Dec 2018 19:23:48 GMTKumshan4502018-12-17T19:23:48ZDax measure to calculate time difference between two data/time values
https://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/586306#M277878
<P>Require your help in finding out time difference between two dates in the same column.</P><P> </P><P>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).</P><P> </P><P>I have attached the sample data.</P><P> </P><P>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).</P><P> </P><UL><LI>First, do multiple sorting by Column collected by and then by column Collection DTM.</LI></UL><P> <span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Screee.jpg" style="width: 588px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/137322i3C7A29A59FCF4F4F/image-size/large?v=v2&px=999" role="button" title="Screee.jpg" alt="Screee.jpg" /></span></P><P> </P><UL><LI>Second create new column (TimeSpan) and apply new formula. <UL><LI>collection(dtm)- previous(Collection(dtm))</LI></UL></LI><LI>Third apply below formula in another column:<UL><LI>if(Collectedby<>previous(Collectedby),0, if(TImeSpan<60 minutes),1,0)</LI></UL></LI></UL><P>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.</P><P> </P><P>So interested in doing it in DAX measures without creating any additonal columns in power pivot data model.</P><P> </P><P>Please find attatched the sample data for your perusal </P><P> </P><P><A href="https://drive.google.com/folderview?id=1gZit23aVRwC5aVMTp7ohiBeSx14rwBFZ" target="_blank">https://drive.google.com/folderview?id=1gZit23aVRwC5aVMTp7ohiBeSx14rwBFZ</A></P>Sun, 16 Dec 2018 03:51:55 GMThttps://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/586306#M277878Kumshan4502018-12-16T03:51:55ZRe: Dax measure to calculate time difference between two data/time values
https://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/586317#M277886
<P>Did you try using Datediff? </P><P> </P><P><A href="https://docs.microsoft.com/en-us/dax/datediff-function-dax" target="_self">https://docs.microsoft.com/en-us/dax/datediff-function-dax</A></P>Sun, 16 Dec 2018 07:30:09 GMThttps://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/586317#M277886Omega2018-12-16T07:30:09ZRe: Dax measure to calculate time difference between two data/time values
https://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/586744#M278107
<P>Hi Kumshan450,</P><P> </P><P>"</P><UL><LI>Second create new column (TimeSpan) and apply new formula. <UL><LI>collection(dtm)- previous(Collection(dtm))</LI></UL></LI><LI>Third apply below formula in another column:<UL><LI>if(Collectedby<>previous(Collectedby),0, if(TImeSpan<60 minutes),1,0)</LI></UL></LI></UL><P>"</P><P> </P><P><--- Are collection and previous a function or a table you've created? What's "Collectedby"? In addtion, could you share your pbix file via onedrive for further analysis?</P><P> </P><P>Regards,</P><P>Jimmy Tao</P>Mon, 17 Dec 2018 09:49:00 GMThttps://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/586744#M278107v-yuta-msft2018-12-17T09:49:00ZRe: Dax measure to calculate time difference between two data/time values
https://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/587098#M278296
<P>Sorry i am accepting solution on subtracting current row from previous row using dax measures technique not calculated column. </P><P> </P><P>I am able to crack the solution by creating calculated column. However already mentioned data size is huge. Hence interest in using dax measures. </P><P> </P><P>I am able to create calculated column. below is the code. Please replicate the above code using dax measure not calculated column. </P><P> </P><DIV><DIV><SPAN>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))</SPAN></DIV></DIV>Mon, 17 Dec 2018 19:23:48 GMThttps://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/587098#M278296Kumshan4502018-12-17T19:23:48ZRe: Dax measure to calculate time difference between two data/time values
https://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/587102#M278300
<P>Sorry i am accepting solution on subtracting current row from previous row using dax measures technique not calculated column. </P><P> </P><P>I am able to crack the solution by creating calculated column. However already mentioned data size is huge. Hence interest in using dax measures. </P><P> </P><P>I am able to create calculated column. below is the code. Please replicate the above code using dax measure not calculated column. </P><P> </P><DIV><DIV><SPAN>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))</SPAN></DIV><DIV> </DIV><DIV><SPAN>Sample data file link</SPAN></DIV><DIV> </DIV><DIV><P><A href="https://drive.google.com/file/d/0B6MyrMP8NEt1MmFYMkNvOUNyRGtCVFFSOWhNSFczbUszTVA4/view?usp=drivesdk" target="_blank">https://drive.google.com/file/d/0B6MyrMP8NEt1MmFYMkNvOUNyRGtCVFFSOWhNSFczbUszTVA4/view?usp=drivesdk</A></P></DIV></DIV>Mon, 17 Dec 2018 19:28:23 GMThttps://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/587102#M278300Kumshan4502018-12-17T19:28:23ZRe: Dax measure to calculate time difference between two data/time values
https://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/587115#M278309
<P>Additional information, I have used rankx function to sort the data..</P><P> </P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Rankx function to sort the data in ascending order" style="width: 600px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/137545i16C11D5F5FB3DE62/image-size/large?v=v2&px=999" role="button" title="Capture.JPG" alt="Rankx function to sort the data in ascending order" /><span class="lia-inline-image-caption" onclick="event.preventDefault();">Rankx function to sort the data in ascending order</span></span></P><P> </P><P>Then created a Time Span less than one minute column using below code. </P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Time Span less than one minute calculated column" style="width: 600px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/137547i81B513D50F47630E/image-size/large?v=v2&px=999" role="button" title="Timespan.JPG" alt="Time Span less than one minute calculated column" /><span class="lia-inline-image-caption" onclick="event.preventDefault();">Time Span less than one minute calculated column</span></span></P><P>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.</P><P> </P>Mon, 17 Dec 2018 19:45:20 GMThttps://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/587115#M278309Kumshan4502018-12-17T19:45:20ZRe: Dax measure to calculate time difference between two data/time values
https://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/593645#M281815
<P><LI-USER uid="65852"></LI-USER>,</P>
<P> </P>
<P>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:</P>
<P> </P>
<PRE>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
)
)
</PRE>
<P>Regards,</P>
<P>Jimmy Tao</P>Wed, 02 Jan 2019 09:25:39 GMThttps://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/593645#M281815v-yuta-msft2019-01-02T09:25:39ZRe: Dax measure to calculate time difference between two data/time values
https://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/594246#M282144
<P>Hi Thank you very much for providing the code.</P><P> </P><P>I have put your code (<STRONG>measure name</STRONG>: TS) and compared the result with the calculated column result (TS<1min). Its not matching. </P><P> </P><P>Please find here the screenshot. </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Sample picture.JPG" style="width: 219px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/139341i80030BB5999F1EAE/image-size/large?v=v2&px=999" role="button" title="Sample picture.JPG" alt="Sample picture.JPG" /></span></P><P> </P><P>I have send the file data link. You can refer my previous post. </P><P> </P>Thu, 03 Jan 2019 06:04:00 GMThttps://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/594246#M282144Kumshan4502019-01-03T06:04:00ZRe: Dax measure to calculate time difference between two data/time values
https://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/1215333#M541310
<P>Also can use a measure, i feel little faster loading time with this </P><P>I use this for time tracking in call center, </P><P><BR /><SPAN>TimeDiff = CONVERT(SELECTEDVALUE(Table[Date1],0)-SELECTEDVALUE(Table[Date2],0),DATETIME)</SPAN></P><DIV> </DIV><DIV><SPAN>and then change the format to time hh:mm:ss. You can create additional measure based on this </SPAN></DIV><DIV><SPAN><BR />Cheers</SPAN></DIV>Fri, 10 Jul 2020 17:12:34 GMThttps://community.powerbi.com/t5/Desktop/Dax-measure-to-calculate-time-difference-between-two-data-time/m-p/1215333#M541310Anonymous2020-07-10T17:12:34Z