How to create aMeasure/Column to show the duration of time since last entry, with multiple condition

[wiselyman3]

Hi All, this may be a confusing one so bear with me. I have a table that logs changes that employees have made to the workgroups they are in, and the date/time that these changes were made. I am in need of adding a column that calculates the total amount of time that they were out of each workgroup when they activate themselves back in it. Please see below for samples data and expected result: 

Sample: 

User	Change	Workgroup	Date/Time
Sam	Out	Red	1/1/25 1:00 PM
Alex	Out	Red	1/1/25 1:30 PM
Sam	Out	Green	1/1/25 1:45 PM
Tina	Out	Red	1/1/25 2:00 PM
Tina	Out	Green	1/1/25 2:30 PM
Alex	In	Red	1/1/25 3:00 PM
Alex	Out	Green	1/1/25 3:15 PM
Tina	In	Red	1/1/25 3:30 PM
Sam	In	Red	1/1/25 8:00 PM
Sam	In	Green	1/1/25 8:30 PM
Alex	In	Green	1/1/25 9:00 PM
Tina	In	Green	1/2/25 8:00 AM

In this sample, "Out" means that they have taken themselves out of that workgroup. "In" means they have placed themselves back in to that workgroup. I am looking for a column that shows the total duration they were "Out" of that workgroup, and the duration of that will be displayed on the row that they went back "In" to it. Expected result for this sample data is below: 

User	Change	Workgroup	Date/Time	Time Out of Workgroup
Sam	Out	Red	1/1/25 1:00 PM	N/A
Alex	Out	Red	1/1/25 1:30 PM	N/A
Sam	Out	Green	1/1/25 1:45 PM	N/A
Tina	Out	Red	1/1/25 2:00 PM	N/A
Tina	Out	Green	1/1/25 2:30 PM	N/A
Alex	In	Red	1/1/25 3:00 PM	2 hrs
Alex	Out	Green	1/1/25 3:15 PM	N/A
Tina	In	Red	1/1/25 3:30 PM	1 hr 30 mins
Sam	In	Red	1/1/25 8:00 PM	7 hrs
Sam	In	Green	1/1/25 8:30 PM	6 hrs 45 mins
Alex	In	Green	1/1/25 9:00 PM	5 hrs 45 mins
Tina	In	Green	1/2/25 8:00 AM	17 hrs 30 mins

As you can see, I need it to look back to the last "Out" entry for that specific User AND workgroup. The logic is that for example, "Sam" goes Out of the Red workgroup at 1/1/25 1:00 pm. He then goes back In to the Red workgroup at 1/1/25 8:00 pm. This gives us a time of 7 hrs of Sam being Out of the Red workgroup, shown in the expected result column. Hopefully this is clear and I appreciate the help! 

[Cookistador · Accepted solution]

Hi @wiselyman3 

After some tests, this is what I achieve

This is the dax code I used

Time Out of Workgroup =

VAR CurrentUser = [User]

VAR CurrentWorkgroup = [Workgroup]

VAR CurrentDateTime = [Date/Time]

VAR CurrentChange = [Change]

RETURN

    IF(

        CurrentChange = "In",

        VAR LastOutDateTime =

            CALCULATE(

                MAXX(

                    FILTER(

                        ALL('Table'),

                        'Table'[User] = CurrentUser &&

                        'Table'[Workgroup] = CurrentWorkgroup &&

                        'Table'[Change] = "Out" &&

                        'Table'[Date/Time] < CurrentDateTime

                    ),

                    'Table'[Date/Time]

                ),

                ALLEXCEPT('Table', 'Table'[User], 'Table'[Workgroup])

            )

        RETURN

            IF(

                NOT ISBLANK(LastOutDateTime),

                VAR DurationInSeconds = DATEDIFF(LastOutDateTime, CurrentDateTime, SECOND)

                VAR Hours = INT(DurationInSeconds / 3600)

                VAR Minutes = INT(MOD(DurationInSeconds, 3600) / 60)

                RETURN

                    IF(

                        Hours = 0 && Minutes = 0,

                        "N/A",

                        FORMAT(Hours, "0") & " hrs " & FORMAT(Minutes, "0") & " mins"

                    )

            )

    )

Do no hesistate to ask if something is not working

[wiselyman3]

Hi, 

When I try this, I get this error: 

"The value for 'User' cannot be determined. Either the column doesn't exist, or there is no current row for this column.

I have made sure Table and all of the column names match the dax, but I am still getting this error

[Cookistador]

Hi 

Sorry I didn't mentioned it but it is a calculated column not a measure 

If it's not working with a calculated column, can you try to add your table name before the field 

[wiselyman3]

Got it, this works perfectly. Thank you!!