Forum Discussion

mgaut341's avatar
mgaut341
Helper II
6 months ago
Solved

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

 

Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    6 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 )
)

     

4 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    6 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 )
)

     

    • mgaut341's avatar
      mgaut341
      Helper II
      6 months ago

      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's avatar
        dk_dk
        Super User
        6 months ago

        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"