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
Anonymous
Not applicable

Time difference between date time values in same column based on a value in a different column

Hello,

I am trying to aggregate the time a user has spent in the Receipt function scanning items in the warehouse. How would I get the output in the following table (elapsed seconds):

 

Assignment 1UserDate Time StartFunctionElapsed Seconds
1JF9/30/19 8:16 AMRECEIPT0
1JF9/30/19 8:17 AMRECEIPT60
2MK10/1/19 4:04 PMRECEIPT0
2MK10/1/19 4:10 PMRECEIPT0
2MK10/1/19 4:11 PMRECEIPT420

 

Essentially, I need to aggregate the elapsed seconds by User for the Receipt function. I know I need to use the DATEDIFF between the minimal date/time and max date time for the user that is completing the receipt function, but I am not sure how to have 0 for all values except for the last one (the aggregated value). I guess I could have the number of seconds between each date since power bi will aggregate it anyway. So, to have:

 

Assignment 1UserDate Time StartFunctionElapsed Seconds
1JF9/30/19 8:16 AMRECEIPT0
1JF9/30/19 8:17 AMRECEIPT60
2MK10/1/19 4:04 PMRECEIPT0
2MK10/1/19 4:10 PMRECEIPT360
2MK10/1/19 4:11 PMRECEIPT60

 

The receipt function is not the only function in the dataset, so I would need to filter to that function and aggregate the time by user.

I appreciate the help!

1 ACCEPTED SOLUTION

Hi @Anonymous ,

Here is the pbix PBIX 
I put both columns in so that you have your choice, the second column only shows in the last row of the user. The difference between the measure and the column at the high level, is that with a measure, the engine does not have row context, whereas in the table it does. (It knows which row it is operating on.) Therefore it was just a matter of dropping MAX().


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
elapsed columns.PNG

 





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

Proud to be a Super User!




View solution in original post

11 REPLIES 11
Nathaniel_C
Super User
Super User

Hi @Anonymous 
What is the difference between the two tables?
Nathaniel





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

Proud to be a Super User!




Hi @Anonymous ,

If I understand correctly this is what you are looking as an output? 
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel


seconds.PNG

 

 

 

lapse Secs Calc = 
VAR _curTime =
    MAX ( TimeElapse[Date Time Start] )
VAR _pasttime =
    CALCULATE (
        MAX ( TimeElapse[Date Time Start] ),
        TimeElapse[Date Time Start] < _curTime,
        ALLEXCEPT ( TimeElapse, TimeElapse[User], TimeElapse[Function] )
    )
VAR _dif =
    DATEDIFF ( _pasttime, _curTime, SECOND )
RETURN
    _dif

 



 





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

Proud to be a Super User!




Anonymous
Not applicable

Hi @Nathaniel_C ,

I just imported my example data into a new file and ran your function on it but my results do not match to what you had.

 

 

Capture.PNG

Hi @Anonymous ,

Try it as a measure, not a calculated column.  Looking at your first picture that is what i built it for. Put the first 5 columns on a table, then add the measure.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





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

Proud to be a Super User!




Anonymous
Not applicable

Hi! Sorry for not specifying that i needed it as a calculated column, because I am doing some other calculations based on the elapsed time. When I return the past time, it is incorrectly returning the time per user. For example, for JF, it should return 

9/30/2019 8:16:00 AM and for the MK it should return 10/1/2019 4:11:00 PM. I feel like if I could get this working, the rest of the function would be correct.

 

I appreciate the help!

 

2.PNG

Hi @Anonymous ,

Here is the pbix PBIX 
I put both columns in so that you have your choice, the second column only shows in the last row of the user. The difference between the measure and the column at the high level, is that with a measure, the engine does not have row context, whereas in the table it does. (It knows which row it is operating on.) Therefore it was just a matter of dropping MAX().


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
elapsed columns.PNG

 





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

Proud to be a Super User!




Anonymous
Not applicable

Nathaniel,

 

I have a similar problem. I have a restaurant reservations table. Basically there are just two columns that should go into calculation. PersonName and ReservationDate. I need to calculate the time difference between reservations of the same cusstomer. There may be customers who only reserved once so we don't count them. Only repeating customers count. They may have from two records to many. How can I do that in DAX?

Thansk,

Stan

Anonymous
Not applicable

Thanks so much for your help! This worked! 

Hi @Anonymous ,

You are welcome!

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





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

Proud to be a Super User!




Hi @Anonymous 
Or with zeros.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathanielseconds.PNG

 

Elapse Secs Calc = 
VAR _curTime =
    MAX ( TimeElapse[Date Time Start] )
VAR _pasttime =
    CALCULATE (
        MAX ( TimeElapse[Date Time Start] ),
        TimeElapse[Date Time Start] < _curTime,
        ALLEXCEPT ( TimeElapse, TimeElapse[User], TimeElapse[Function] )
    )
VAR _dif =
    IF(ISBLANK(_pasttime), 0 ,DATEDIFF ( _pasttime, _curTime, SECOND ))
RETURN
    _dif

 





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

Proud to be a Super User!




Hi @Anonymous ,

Here is the solution with only the final value showing for each User and Function.
cl1.PNG

 

Only latest value = 
VAR _calc = [Cummulative]
VAR _maxDate =
    CALCULATE (
        MAX ( TimeElapse[Date Time Start] ),
        ALLEXCEPT ( TimeElapse, TimeElapse[User], TimeElapse[Function] )
    )
RETURN
    IF ( MAX ( TimeElapse[Date Time Start] ) = _maxDate, [Cummulative], 0 )


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





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

Proud to be a Super User!




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.

Top Solution Authors