Date difference with multiple criteria

[mgaut341]

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: Spanish Training, and then another date difference for when they attend the Interpreter: Spanish Training to their first Interpreter: Spanish Remote.

[cengizhanarslan · Accepted solution]

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]

It worked for Orientation to training, but for training to first remote it gives me a result of -8 days which is not accurate

[dk_dk]

Hi @mgaut341 
I did a quick check copying the measures from @cengizhanarslan and I get the correct results across the board:

For which Volunteer are you getting -8?
Could you double check that your date column is parsed correctly in Power BI, and then maybe as a troubleshooting step check the values of the 3 helper measures like so:

Edit: In case the screenshot you shared is how the data looks like exactly in power bi, you would need to fill down the empty cells in the Volunteer column for this solution to work correctly.
You can do this in power query: Fill down - MS Learn 

Below is the sample query I used, you may paste this into a blank query and compare to your results:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZM/CwIxDMW/SulsIK2eem4ugiAoKi6HQ9GghdpKraDf3h7+4Sa9TEKn8Httk/dSVXJBiaLsyE1wV5+IophHSz6ZZIPPdewCDkCjLuS2U8lcmWYsnmOtG4nV2Xh7OYp1NNZbf3gqVPFbsaRTSFTzPcgSFq9LDl8ADjl8HzTr/3k8PQ4/BKVb8MGRWDhzFxOzs85mR0J8yfuc50qeHUpBPh9+7Oj2LR4lYIvbG/FQCIpjR54Vq996to04zazfm/82gKBbbFDTANY+1IYhk2f9RwMqJv/O9/YB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Volunteer = _t, #"Service Assignment" = _t, #"Service From Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Volunteer", type text}, {"Service Assignment", type text}, {"Service From Date", type date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Volunteer"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Volunteer"})
in
    #"Filled Down"

[mgaut341]

Yup, it was in fact correct. I was getting negative results when looking for the average but solved that.