cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
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

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

Resolver I

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.

Solution Sage

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors