Get Min and Max Date Based on Batch Number and Employee

[junmin]

Hi all, 

As the subject suggests, I am trying to get the minimum and maximum date based on batch number and employee. My dataset contains every transaction an employee made. So an example of my dataset looks like this:

DATE | Employee ID | Batch Number | Task 

10-Dec-2023 22:01:00 | 123456 | 000001 | Task A

10-Dec-2023 22:13:00 | 123456 | 000001 | Task B

10-Dec-2023 22:20:00 | 123456 | 000001 | Task C

10-Dec-2023 22:55:00 | 123456 | 000001 | Task D

11-Dec-2023 8:00:00 | 123456 | 000002 | Task A

11-Dec-2023 8:10:00 | 123456 | 000002 | Task B

11-Dec-2023 8:11:00 | 123456 | 000002 | Task C

11-Dec-2023 8:30:00 | 123456 | 000002 | Task D

One Employee can perform Tasks on several batches at different times

One Batch number has several Tasks, but only one Employee, which means, for example, Batch# 000002 can only be assigned to Employee ID# 123456

So, now im looking for the min and max date per Batch per Employee because i want to calculate the duration for an Employee to complete a Batch. 

Taking the above dataset as example, I would expect something like this:

DATE | Employee ID | Batch Number | Task | Min Date | Max Date | Duration

10-Dec-2023 22:01:00 | 123456 | 000001 | Task A | 10-Dec-2023 22:01:00 | 10-Dec-2023 22:55:00 | 54 min

10-Dec-2023 22:13:00 | 123456 | 000001 | Task B | 10-Dec-2023 22:01:00 | 10-Dec-2023 22:55:00 | 54 min

10-Dec-2023 22:20:00 | 123456 | 000001 | Task C | 10-Dec-2023 22:01:00 | 10-Dec-2023 22:55:00 | 54 min

10-Dec-2023 22:55:00 | 123456 | 000001 | Task D | 10-Dec-2023 22:01:00 | 10-Dec-2023 22:55:00 | 54 min

11-Dec-2023 8:00:00 | 123456 | 000002 | Task A | 11-Dec-2023 8:00:00 | 11-Dec-2023 8:30:00 | 30 min

11-Dec-2023 8:10:00 | 123456 | 000002 | Task B | 11-Dec-2023 8:00:00 | 11-Dec-2023 8:30:00 | 30 min

11-Dec-2023 8:11:00 | 123456 | 000002 | Task C | 11-Dec-2023 8:00:00 | 11-Dec-2023 8:30:00 | 30 min

11-Dec-2023 8:30:00 | 123456 | 000002 | Task D | 11-Dec-2023 8:00:00 | 11-Dec-2023 8:30:00 | 30 min

I have tried to create calculated column using this in the query:

ColumnEarliestDate =

CALCULATE(

    MIN('Table'[DATE]),

    ALLEXCEPT('Table',

    'Table'[Employee ID], 'Table'[Batch Number])

)

ColumnLatestDate =

CALCULATE(

    MAX('Table'[DATE]),

    ALLEXCEPT('Table',

    'Table'[Employee ID], 'Table'[Batch Number])

)

ColumnDuration = DATEDIFF('Table'[ColumnEarliestDate], 'Table'[ColumnLatestDate], MINUTE)

But it isn't giving me what I'm looking for. i don't even know what number it is giving me, doesn't make sense 😞 

I would truly appreciate any help i could get, thank you so much!!

[amitchandak · Accepted solution]

@junmin , I think task needs to be part of that

ColumnEarliestDate =
CALCULATE(
MIN('Table'[DATE]),
ALLEXCEPT('Table',
'Table'[Employee ID], 'Table'[Batch Number], 'Table'[Task])
)

ColumnLatestDate =
CALCULATE(
MAX('Table'[DATE]),
ALLEXCEPT('Table',
'Table'[Employee ID], 'Table'[Batch Number], 'Table'[Task])
)

ColumnDuration = DATEDIFF('Table'[ColumnEarliestDate], 'Table'[ColumnLatestDate], MINUTE)

better you to have measure with required columns in table visual. Same can work as measure

You can also consider earlier for column

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

Or Try measures like

MinDateWithTask =
CALCULATE(
MIN('Table'[DATE]),
ALLSELECTED('Table'),
'Table'[Employee ID] = MAX('Table'[Employee ID]),
'Table'[Batch Number] = MAX('Table'[Batch Number]),
'Table'[Task] = MAX('Table'[Task])
)

MaxDateWithTask =
CALCULATE(
MAX('Table'[DATE]),
ALLSELECTED('Table'),
'Table'[Employee ID] = MAX('Table'[Employee ID]),
'Table'[Batch Number] = MAX('Table'[Batch Number]),
'Table'[Task] = MAX('Table'[Task])
)

DurationWithTask =
DATEDIFF(
[MinDateWithTask],
[MaxDateWithTask],
MINUTE
)

[junmin]

hi amitchandak, thank you for your reply. May I pls know why I must include tasks as well? So, if I were to have other columns (besides the one i showed here), do I have to include all of them as well?

thank you.