Minute Datediff with date and time in separate columns

[GabrielSantos]

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?

[amitchandak]

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]

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.

[GabrielSantos]

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.

[kentyler]

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.