Filter Group By in Same table

[slanka]

Friends,

I have data in one single table, Table A, in the below format,

EmpName	ProjName	TimesheetDate
Emp1	Proj1	1-Jul-19
Emp1	Proj1	2-Jul-19
Emp1	Proj1	3-Jul-19
Emp1	Proj1	5-Jul-19
Emp1	Proj1	8-Jul-19
Emp1	Proj2	9-Jul-19
Emp1	Proj2	10-Jul-19
Emp1	Proj2	11-Jul-19
Emp1	Proj2	12-Jul-19
Emp1	Proj2	15-Jul-19
Emp1	Proj2	16-Jul-19
Emp1	Proj2	17-Jul-19
Emp1	Proj2	18-Jul-19
Emp1	Proj2	19-Jul-19
Emp1	Proj3	24-Jul-19
Emp1	Proj3	25-Jul-19
Emp1	Proj3	26-Jul-19

I need to filter that data and get the MinDate and MaxDate per Employee, Per Project (everything is in the same table, tried creating Calculate(Min(Timesheedate),Filter(TableA,Groupby(empname)), it's giving circular dependency error).

Final expected output should be,

EmpName	ProjName	MinDate	MaxDate
Emp1	Proj1	1-Jul-19	8-Jul-19
Emp1	Proj2	9-Jul-19	19-Jul-19
Emp1	Proj3	24-Jul-19	26-Jul-19

Need help!

[Anonymous]

Hi @slanka 

Here is my solution

Create 3 new columns:

Column MAX = 
CALCULATE ( MAX ( T15[TimesheetDate] ), ALLEXCEPT ( T15, T15[EmpName],T15[ProjName] ) )

Column Min = 
CALCULATE ( MIN( T15[TimesheetDate] ), ALLEXCEPT ( T15, T15[EmpName],T15[ProjName] ) )

NOT Mandatory, just for debugging
MIN_MAX = 
IF(T15[Column MAX] = T15[TimesheetDate] || T15[Column Min]  = T15[TimesheetDate], 1,0)

Create a new table:

T16 = 
SUMMARIZE(T15,T15[EmpName], T15[ProjName], T15[Column MAX],T15[Column Min])

Good Luck!
A

[HotChilli · Accepted solution]

Drag EmpName and ProjName on to a table visualisation.

Then drag TimeSheetDate on to the same table twice.

In the Values field well, click the dropdown on the first TimeSheetDate, choose Earliest.

Click the dropdown on the secondTimeSheetDate, choose Latest.

You can change the date format by choosing TimeSheetDate instead of Date Hierarchy from the same dropdown.

[slanka]

Thanks for the trick 🙂 it worked!