Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
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
thanks for this - I am getting an erro message regarding minimum of 2 arguments
@psabbag wrote:thanks for this - I am getting an erro message regarding minimum of 2 arguments
Try once again the measure above.
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
52 | |
33 | |
19 | |
16 | |
15 |
User | Count |
---|---|
94 | |
80 | |
37 | |
22 | |
18 |