Calculating date difference across rows with a criteria

[samlittleTT]

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!

[v-yuta-msft · Accepted solution]

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...

Regards,

Jimmy Tao

[samlittleTT]

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)

[v-yuta-msft]

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

[CoreyP]

Why don't you try adding another column, "End Date." 

Then, a calculated column with:

Difference =

DATEDIFF(
                    [Start Date], [End Date], DAY
            )

[samlittleTT]

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!