Get the First activity based on date column

[Sri_phani]

Hi, 

I have been trying this for a long time, but I haven't been able to do this.  please help me with this. 

Requirement: I have to show agents today's first break period in a Card visual.  
Tables in Dataset - Activeagents, Verint table. 

Relationship: Activeagents to Verint Table (1 to Many). 

Condition - The page has an Employee Name slicer, for the selected employee name, based on shift date filter today's date and filter Break, then based on activity start time first minium value to show the Activity timesbetween(result is like 04:00 PM - 04:15 PM IST)

Here is the sample data of single employee : https://docs.google.com/spreadsheets/d/17wFnZqJb6fDiMZcek_L3k0EipAGssLYRgpGKKMXeXRs/edit?usp=sharing 

(I want the value which is highlighted in Yellow and Red in Card visual)

Here is the DAX code which I am using = 

Firstbreak =

VAR TodayDate = TODAY()

VAR SelectedEmployee = SELECTEDVALUE(ActiveAgentsFiltered[Employee Name])

// Step 1: Filtered Table

VAR Filtered =

    FILTER(

        Verint,

        Verint[Shift Date] >= TodayDate &&

        Verint[Shift Date] < TodayDate + 1 &&

        CONTAINSSTRING(Verint[Activity Name], "Break") &&

        Verint[Employee Name as per IROZ] = SelectedEmployee

    )

// Step 2: Minimum Start Time

VAR MinimumStartTime =

    CALCULATE(

        MIN(Verint[Activity Start time]),

        Filtered

    )

// Step 3: Corresponding Activity Time Between

VAR ActivityTime =

    CALCULATE(

        FIRSTNONBLANK(Verint[Activity Timebetween], 1),

        Verint[Activity Start time] = MinimumStartTime,

        Verint[Employee Name as per IROZ] = SelectedEmployee

    )

// Return the Result

RETURN

ActivityTime

Appreciate your help. 

[Greg_Deckler · Accepted solution]

@Sri_phani Try this. PBIX is attached below signature.

Measure = 
    VAR __Emp = MAX('Table'[Employee Name as per IROZ])
    VAR __Table = FILTER( 'Table', [Extracted Activity Name] = "Break_Unpaid" )
    VAR __MaxBreakStart = MAXX( __Table, [Activity start IST/EET] )
    VAR __MaxBreakEnd = MAXX( FILTER( 'Table', [Activity start IST/EET] = __MaxBreakStart ), [Activity end IST/EET] )
    VAR __BreakStartTime = __MaxBreakStart - TRUNC( __MaxBreakStart )
    VAR __BreakEndTime = __MaxBreakEnd - TRUNC( __MaxBreakEnd )
    VAR __Result = FORMAT(__BreakStartTime, "Medium time" ) & " - " & FORMAT( __MaxBreakEnd, "Medium time" )
RETURN
    __Result

[Anonymous · Accepted solution]

hi @Sri_phani ,

as per the data available in the mentioned link the minimum time for break on 26th Jul should be 12.45 PM.

After considering this, if I proceed then I have done the following steps

Filter the table with respect to Break_Unpaid

Calculating first break

Please check visualization

Now if you need the yellow/red highlighted time as first break then either max function or distinctcount function (if there are more than 2 break periods available for an employee per day) needs to be used.
Using max function

Using Distinctcount function

Visualization can be done in the similar way.

If this solves your problem then accept the same as your solution.

[Sri_phani]

You solution as well worked for me. Thank you Rakesh.!

[Anonymous]

@Sri_phani if it works then it is requested you to accept the same as solution. It will be helpful for me too.

[Greg_Deckler · Accepted solution]

@Sri_phani Try this. PBIX is attached below signature.

Measure = 
    VAR __Emp = MAX('Table'[Employee Name as per IROZ])
    VAR __Table = FILTER( 'Table', [Extracted Activity Name] = "Break_Unpaid" )
    VAR __MaxBreakStart = MAXX( __Table, [Activity start IST/EET] )
    VAR __MaxBreakEnd = MAXX( FILTER( 'Table', [Activity start IST/EET] = __MaxBreakStart ), [Activity end IST/EET] )
    VAR __BreakStartTime = __MaxBreakStart - TRUNC( __MaxBreakStart )
    VAR __BreakEndTime = __MaxBreakEnd - TRUNC( __MaxBreakEnd )
    VAR __Result = FORMAT(__BreakStartTime, "Medium time" ) & " - " & FORMAT( __MaxBreakEnd, "Medium time" )
RETURN
    __Result