Forum Discussion
Calculating date difference across rows with a criteria
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,
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!