cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GabrielSantos
Resolver I
Resolver I

Minute Datediff with date and time in separate columns

Hey all,

 

For compression purposes, I have split a datetime value into both a date and a time value on the fact table. However, I am struggling with the concept of performing a datediff between any two given fact records given the fact that I would first have to reconstruct the value as a datetime.

 

My data structure is as follows:

Three column table:


IndividualKey ,

[Activity Start Date] ,

[Activity Start Time]

 

If I wanted to find the number of minutes between a person's first and last activity, how would that be accomplished in DAX?

4 REPLIES 4
amitchandak
Super User
Super User

I think you combine the date and time field.

 

new column = format(Activity date,"mm/dd/yyyy ") & format(Activity Time," HH:mi:ss")

And create a new column using date datediff to get difference in Minutes

https://docs.microsoft.com/en-us/dax/datediff-function-dax

kentyler
Solution Sage
Solution Sage

Does their activity cross over days ?

Often in these kinds of situations it helps to have a Time table with all the minutes for a day, just like people often have a date table for dates. You can have a column in the time table that tells the minute in the day...an integer number that increases for every minute.

You can use the minute of the day number to do easy math to get total minutes and then divide that to get hours if you want.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Thanks @kentyler .

Yes, the activities often cross over several different days. I agree with the value of a time table that I can join to the fact table, but I'm still unsure as of how I can use that to help return a Datediff in minutes between two given activities. It seems I would need to preserve some context from a given record to return the datetime.

 

 

Yes, that is a standard problem in DAX, since it has no native concept of next record or previous record.

The standard solution is to store the value in the record you're starting from in a VAR

VAR current_time = SELECTEDVALUE(mytable[mytime])
and then lookup the other value you want to compare it with.

if the records are sorted and you want to compare to the next record it's often simpler to user power query to add a numerical index. Then you can get the current index and just add 1 or subtract 1.

but if that's not practical you can use FILTER possibly to find the matching record you want to compare to and retrieve the values from that.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Helpful resources

Announcements
March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

Power BI Dev Camp Session 32

Ted's Dev Camp - March 30, 2023

In this session, we'll examine important design issues and decisions that arise when developing with Power BI embedding.

Top Solution Authors