Forum Discussion
Calculating date difference across rows with a criteria
Hi,
I'm having a little trouble calculating the length of an activity for my project schedules; I am trying to get the number of days between two dates (one the start of Gate 1 and the other the start of Gate 2). My Data looks like this:
Project ID | Gate Number | Start Date | Index
1 Gate 1 1/2/2018 1
1 Gate 2 4/13/2018 2
1 Gate 3 5/27/2018 3
2 Gate 1 4/19/2018 4
2 Gate 2 4/30/2018 4
With some help from the forums i've cobbled together this but i cant figure out how to only use the index number above when the project ID is the same?
Difference = var A = CALCULATE( MAX('table'[index]))-1 return If('table'[index]=0,'table'[start date], 'table'[start date] - CALCULATE(SUM('table'[start date]),FILTER('table','table'[index] = A)))
Appreciate any help!
Hi samlittleTT,
To achieve your requirement, create a measure using DAX formula like this:
Diff = VAR gate1_start = CALCULATE(MIN('table'[Start Date]), 'table'[Gate Number]= "Gate1") VAR gate2_start = CALCULATE(MIN('table'[Start Date]), 'table'[Gate Number]= "Gate2") RETURN DATEDIFF(gate1_start, gate2_start, DAY)PBIX file here: https://www.dropbox.com/s/yxmkmq1w9bnwffp/Calculating%20date%20difference%20across%20rows%20with%20a%20criteria.pbix?dl=0
Regards,
Jimmy Tao
5 Replies
Hi,
I'm having a little trouble calculating the length of an activity for my project schedules; I am trying to get the number of days between two dates (one the start of Gate 1 and the other the start of Gate 2). My Data looks like this:
Project ID | Gate Number | Start Date | Index
1 Gate 1 1/2/2018 1
1 Gate 2 4/13/2018 2
1 Gate 3 5/27/2018 3
2 Gate 1 4/19/2018 4
2 Gate 2 4/30/2018 4
With some help from the forums i've cobbled together this but i cant figure out how to only use the index number above when the project ID is the same?
Difference = var A = CALCULATE( MAX('table'[index]))-1 return If('table'[index]=0,'table'[start date], 'table'[start date] - CALCULATE(SUM('table'[start date]),FILTER('table','table'[index] = A)))
Appreciate any help!
Why don't you try adding another column, "End Date."
Then, a calculated column with:
Difference = DATEDIFF( [Start Date], [End Date], DAY )Hi samlittleTT,
To achieve your requirement, create a measure using DAX formula like this:
Diff = VAR gate1_start = CALCULATE(MIN('table'[Start Date]), 'table'[Gate Number]= "Gate1") VAR gate2_start = CALCULATE(MIN('table'[Start Date]), 'table'[Gate Number]= "Gate2") RETURN DATEDIFF(gate1_start, gate2_start, DAY)PBIX file here: https://www.dropbox.com/s/yxmkmq1w9bnwffp/Calculating%20date%20difference%20across%20rows%20with%20a%20criteria.pbix?dl=0
Regards,
Jimmy Tao
Hi Jimmy,
Am I right in thinking that to make this expand for more than just gates 1 and 2 I repeat the formula to run through gate 3 also like the below? I would then need to nest the date diff into a IF formula right?
Diff = VAR gate1_start = CALCULATE(MIN('table'[Start Date]), 'table'[Gate Number]= "Gate1") VAR gate2_start = CALCULATE(MIN('table'[Start Date]), 'table'[Gate Number]= "Gate2")
VAR gate3_start = CALCULATE(MIN('table'[Start Date]), 'table'[Gate Number]= "Gate3") RETURN DATEDIFF(gate1_start, gate2_start, DAY)Hi samlittleTT,
Sure, you can add more variables if you need and nest them in IF formula. It's depend on your requirement.
Regards,
Jimmy Tao
