Number of days between milestone dates in same column which relate to another column

[psabbag]

I have a data set where I have a customer name and 3 rows of that customer's milstone dates of 3 key points. I am trying to calculate the number of business days between milestone A & B and between B & C. Sample data below where column D would show the calculated number of business days.

CUSTOMER	TASK	DATE	DAYS BETWEEN MILESTONES
ACME	START	11/1/2020
ACME	DATA COLLECTION	11/20/2020	19
ACME	COMPLETE	12/5/2020	15
ABC WIDGETS	START	10/15/2020
ABC WIDGETS	DATA COLLECTION	10/25/2020	10
ABC WIDGETS	COMPLETE	1/2/2021	69
WORLDWIDE	START	12/25/2020
WORLDWIDE	DATA COLLECTION	1/30/2021	36
WORLDWIDE	COMPLETE	2/6/2021	7
XYZ PLUMBING	START	12/1/2020
XYZ PLUMBING	DATA COLLECTION	1/3/2021	33
XYZ PLUMBING	COMPLETE	2/20/2021	48

[Anonymous]

@psabbag 

I understand you don't need a measure but a calculated column. Here it is:

[Days Between Milestones] = // calculated column
var CurrentCust = T[Customer] // T is your table
var CurrentTask = T[Task]
var Result =
    SWITCH( CurrentTask,
    
        "data collection",
            var DataCollectionTaskDate = T[Date]
            var StartTaskDate =
                MAXX(
                    FILTER(
                        T,
                        T[Customer] = CurrentCust
                        &&
                        T[Task] = "start"
                    ),
                    // The filter guarantees
                    // there'll be only one date.
                    T[Date]
                )
            return
                DataCollectionTaskDate - StartTaskDate,
                
        "complete",
            var CompleteTaskDate = T[Date]
            var DataCollectionTaskDate =
                // The filter guarantees
                // there'll be only one date.
                MAXX(
                    FILTER(
                        T,
                        T[Customer] = CurrentCust
                        &&
                        T[Task] = "data collection"
                    ),
                    T[Date]
                )
            return
                CompleteTaskDate - DataCollectionTaskDate,
        
        
        "start", BLANK()
    )
return
    Result

[psabbag]

thanks for this - I am getting an erro message regarding minimum of 2 arguments

[Anonymous]

---

@psabbag wrote:

thanks for this - I am getting an erro message regarding minimum of 2 arguments

---

Try once again the measure above.

[Jihwan_Kim]

Hi, @psabbag 

Please try the below-calculated measure.

Days Between Milestones =
VAR currentdate =
MAX ( 'Table'[DATE] )
VAR result =
CALCULATE (
MAX ( 'Table'[DATE] ),
FILTER ( ALL ( 'Table' ), 'Table'[DATE] < currentdate ),
VALUES ( 'Table'[CUSTOMER] )
)
RETURN
DATEDIFF ( result, SELECTEDVALUE ( 'Table'[DATE] ), DAY )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.