Forum Discussion
mgaut341
7 months agoHelper II
Date difference with multiple criteria
I am looking to find the date difference it takes people to move up roles. Specifically, how long it takes them from when they attend the Volunteer Orientation to when they attend the Interpreter: S...
- 7 months ago
Step 1 – Get milestone dates per person
You want the first occurrence of each role.
Volunteer → Spanish Training
First Volunteer Orientation = CALCULATE ( MIN ( Assignments[Service From Date] ), Assignments[Service Assignment] = "Volunteer Orientation" )First Spanish Training = CALCULATE ( MIN ( Assignments[Service From Date] ), Assignments[Service Assignment] = "Interpreter: Spanish Training" )First Spanish Remote
First Spanish Remote = CALCULATE ( MIN ( Assignments[Service From Date] ), Assignments[Service Assignment] = "Interpreter: Spanish Remote" )These measures will return the correct first date per Volunteer, as long as Volunteer is on the visual (or in filter context).
Step 2 – Date differences
Orientation → Training
Days: Orientation to Training = VAR StartDate = [First Volunteer Orientation] VAR EndDate = [First Spanish Training] RETURN IF ( NOT ISBLANK ( StartDate ) && NOT ISBLANK ( EndDate ), DATEDIFF ( StartDate, EndDate, DAY ) )Training → First Remote
Days: Training to First Remote = VAR StartDate = [First Spanish Training] VAR EndDate = [First Spanish Remote] RETURN IF ( NOT ISBLANK ( StartDate ) && NOT ISBLANK ( EndDate ), DATEDIFF ( StartDate, EndDate, DAY ) )
cengizhanarslan
7 months agoSuper User
Step 1 – Get milestone dates per person
You want the first occurrence of each role.
Volunteer → Spanish Training
First Volunteer Orientation =
CALCULATE (
MIN ( Assignments[Service From Date] ),
Assignments[Service Assignment] = "Volunteer Orientation"
)First Spanish Training =
CALCULATE (
MIN ( Assignments[Service From Date] ),
Assignments[Service Assignment] = "Interpreter: Spanish Training"
)
First Spanish Remote
First Spanish Remote =
CALCULATE (
MIN ( Assignments[Service From Date] ),
Assignments[Service Assignment] = "Interpreter: Spanish Remote"
)These measures will return the correct first date per Volunteer, as long as Volunteer is on the visual (or in filter context).
Step 2 – Date differences
Orientation → Training
Days: Orientation to Training =
VAR StartDate = [First Volunteer Orientation]
VAR EndDate = [First Spanish Training]
RETURN
IF (
NOT ISBLANK ( StartDate ) && NOT ISBLANK ( EndDate ),
DATEDIFF ( StartDate, EndDate, DAY )
)
Training → First Remote
Days: Training to First Remote =
VAR StartDate = [First Spanish Training]
VAR EndDate = [First Spanish Remote]
RETURN
IF (
NOT ISBLANK ( StartDate ) && NOT ISBLANK ( EndDate ),
DATEDIFF ( StartDate, EndDate, DAY )
)
mgaut341
7 months agoHelper II
It worked for Orientation to training, but for training to first remote it gives me a result of -8 days which is not accurate