Slicer related issuess

[salonibajaj0211]

The requirement is to show the resources and their availability based on projects.
The data consists of employee name, fte, project name, project start and project roll off date.

An employee can be on multiple projects or on a single project. For example, a person can be on two projects with 0.5 FTE each or he can be on a single project with 1 FTE. Also, FTE is not constant, it could be 0.25, 0.5, 1 and so on for employees.

One of the major requirements is to show the available resources and their fte.

For example, Charlie is aligned on projects A and B with 0.5 fte each. He is getting rolled off from project A in June 2023 and from project B in January 2024.

So now I should be able to see Charlie being available from June 2023 with 0.5 fte unless he's aligned on a new project.
Let's say if he's not aligned on any project then if I look at 2024 data then I should be able to see Charlie being available on 1 fte (could show in 2 different rows with 0.5 fte) as he's now rolled off from both the projects.

Attaching the dataset:

Employee ID	Employee Name	Project Name	Status	FTE	Project Start Date	Roll Off Date
101	Charlie	A	Hard Lock	0.5	12-Dec-22	13-Jan-24
101	Charlie	B	Soft Lock	0.5	1-Jan-23	6-Jun-23
203	Steven	B	Soft Lock	0.5	5-Dec-22	1-Jan-24
451	Frank	C	Soft Lock	0.5	1-Jan-23	15-Feb-24
678	George	D	Hard Lock	1	3-Feb-23	21-Nov-24
970	Hannah	E	Hard Lock	0.5	14-Nov-22	5-Apr-25
970	Hannah	F	Soft Lock	1	18-Dec-22	3-Feb-24
255	Alexis	G	Hard Lock	0.5	12-Oct-22	1-Jan-24
451	Frank	A	Hard Lock	0.5	12-Dec-22	13-Jan-24
623	Matt	B	Soft Lock	0.5	5-Dec-22	1-Jan-24
111	Christine	C	Soft Lock	0.5	1-Jan-23	31-Dec-24
111	Christine	F	Soft Lock	0.5	5-Feb-23	15-Feb-24
111	Christine	G	Soft Lock	0.5	12-Oct-22	1-Jan-24
333	Joy	A	Hard Lock	0.5	12-Dec-22	13-Jan-24
333	Joy	B	Hard Lock	0.5	1-Jan-23	6-Jun-23
333	Joy	D	Hard Lock	0.5	3-Feb-23	21-Nov-24

[bolfri]

Hi,

This is what I would recommend.

Due to fact that one Project Name has different Start and End dates I will consider them as a assign start date and assign end date and for the project i will calculate start date based on first assigment

Step 1. Split this dataset into 3 tables by logic.

dim_emp

dim_project

emp_to_project (+project name [treated as a key], + employee ID)

This is what you should get:

dim_emp

let
Source = Table.SelectColumns(#"SampleData-oryginal",{"Employee Name", "Employee ID"}),
#"Removed Duplicates" = Table.Distinct(Source)
in
#"Removed Duplicates"

dim_project

let
Source = Table.SelectColumns(#"SampleData-oryginal",{"Project Name", "Project Start Date", "Roll Off Date"}),
#"Grouped Rows" = Table.Group(Source, {"Project Name"}, {{"Start Date", each List.Min([Project Start Date]), type nullable date}, {"End Date", each List.Max([Roll Off Date]), type nullable date}})
in
#"Grouped Rows"

emp_to_project

let
Source = #"SampleData-oryginal",
#"Removed Columns" = Table.RemoveColumns(Source,{"Employee Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Project Start Date", "Assignment Start Date"}, {"Roll Off Date", "Assignment End Date"}})
in
#"Renamed Columns"

Step 2) We need additional DAX table for filtering dates. Let's call it dim_calendar that holds all the dates from the lowerst and lastest date in both columns: Project Start Date and Roll Off Date (you can change that if needed).

dim_calendar = 
CALENDAR(
    MIN(FIRSTDATE(dim_project[Start Date]),FIRSTDATE(dim_project[End Date])),
    MAX(LASTDATE(dim_project[Start Date]),LASTDATE(dim_project[End Date]))
)

Important! Remember to change the field type from datetime to date.

Step 3) Connect first 3 tables by their keys. Note that we don't use any relationship to dim_calendar table.

Step 4) Create measures.

Tip: I always create a table that holds my DAX code.

DAX = ROW("DAX","DAX")

Consumed FTE = SUM(emp_to_project[FTE])

Consumed FTE -agg =
var min_date = FIRSTDATE(dim_calendar[Date])
var max_date = LASTDATE(dim_calendar[Date])

return
SUMX(
FILTER('emp_to_project',
AND(
OR(
OR(
//Scenario 1
AND(emp_to_project[Assignment Start Date]<=min_date,emp_to_project[Assignment End Date]>=max_date),
//Scenario 2
AND(emp_to_project[Assignment Start Date]>=min_date,emp_to_project[Assignment End Date]<=max_date)
),
OR(
//Scenario 3
AND(emp_to_project[Assignment Start Date]<=min_date,AND(emp_to_project[Assignment End Date]>=min_date,'emp_to_project'[Assignment End Date]<=max_date)),
//Scenario 4
AND(AND(emp_to_project[Assignment Start Date]>=min_date,'emp_to_project'[Assignment Start Date]<=max_date),'emp_to_project'[Assignment End Date]>=max_date)
)
),
//Exclude all records with wrong data, when start is after end
emp_to_project[Assignment Start Date] <= 'emp_to_project'[Assignment End Date]
)
),
[Consumed FTE]
)

Unused FTE =
IF([Consumed FTE -agg] < 1, 1 - [Consumed FTE -agg])

Step 5) Just build the visualisation that you want.