Find earliest and latest timestamp per day, created calculated column with time in between

Question

I'm fairly new to DAX & PowerBI and I'm trying to wrap my head around how I can determine and calculate timeframes per day.

The Goal:

Get the minimum and maximum timestamps for each day in the data set
Calculate the time difference between the two in HH:MM
Created a calculated column to store the HH:MM result

So far I have found the following two DAX queries to work when creating a calculated column of the min and max timestamps:

CALCULATE(MIN('Packing Jobs - Pick Lines'[Timestamp]),FILTER('Packing Jobs - Pick Lines',FORMAT([Timestamp],"DD-MM-YYYY")=FORMAT(EARLIER([Timestamp]),"DD-MM-YYYY")))

CALCULATE(MAX('Packing Jobs - Pick Lines'[Timestamp]),FILTER('Packing Jobs - Pick Lines',FORMAT([Timestamp],"DD-MM-YYYY")=FORMAT(EARLIER([Timestamp]),"DD-MM-YYYY")))

However I am having a hard time trying to calculate and store the difference between the two results. I do not seem to be able to call the calculated columns when creating a new column to calculate, and when I have tried to combine them into the same column creation, the results are far from accurate.

Column = 
VAR firstscan =
    CALCULATE(MIN('Packing Jobs - Pick Lines'[Timestamp]),FILTER('Packing Jobs - Pick Lines',FORMAT([Timestamp],"DD-MM-YYYY HH:MM")=FORMAT(EARLIER([Timestamp]),"DD-MM-YYYY HH:MM")))
VAR lastscan =
    CALCULATE(MAX('Packing Jobs - Pick Lines'[Timestamp]),FILTER('Packing Jobs - Pick Lines',FORMAT([Timestamp],"DD-MM-YYYY HH:MM")=FORMAT(EARLIER([Timestamp]),"DD-MM-YYYY HH:MM")))

RETURN
    CALCULATE(lastscan - firstscan)

I have tried with and without and HH:MM and the result still shows me in 1899 for every line. Any advice or pointers would be amazing!

PacketLoss PacketLoss · Accepted Answer · 2020-07-15T03:10:34

I've managed to find a solution to this issue via the following;

Created a calculated table, which created a date and email for every distinct user, within the specified time range.

hoursWorked = 
GENERATE(CALENDAR(DATE(2020, 1, 1), DATE(2030, 12, 31)),  SUMMARIZE('Packing Jobs - Pick Lines', 'Packing Jobs - Pick Lines'[Email]))

Created two calculated columns, one to get the first timestamp of the day for each user, and one for the last timestamp each day.

startTime = 
CALCULATE (
    MIN('Packing Jobs - Pick Lines'[Timestamp]),
    FILTER(
            'Packing Jobs - Pick Lines',
            'Packing Jobs - Pick Lines'[Email] = hoursWorked[Email] && FORMAT(hoursWorked[Date], "DD-MM-YYYY") = FORMAT('Packing Jobs - Pick Lines'[Timestamp], "DD-MM-YYYY")
        )
)

endTime = 
CALCULATE (
    MAX('Packing Jobs - Pick Lines'[Timestamp]),
    FILTER(
            'Packing Jobs - Pick Lines',
            'Packing Jobs - Pick Lines'[Email] = hoursWorked[Email] && FORMAT(hoursWorked[Date], "DD-MM-YYYY") = FORMAT('Packing Jobs - Pick Lines'[Timestamp], "DD-MM-YYYY")
        )
)

Finally I calculated a minute value for every day the user has timestamps, storing them all in a final calculated column called minutes.

minutes = 
DATEDIFF(hoursWorked[startTime], hoursWorked[endTime], MINUTE)

To map this with the data in 'Packing Jobs - Pick Lines' I created a relationship between the email field in both tables.

Hope this helps anyone else who may encounter the same issue!

Find earliest and latest timestamp per day, created calculated column with time in between

1 Answers