Forum Discussion

samlittleTT's avatar
samlittleTT
New Member
8 years ago
Solved

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 ...
v-yuta-msft's avatar
v-yuta-msft
Community Support
8 years ago

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

  • samlittleTT's avatar
    samlittleTT
    New Member
    8 years ago

    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's avatar
      v-yuta-msft
      Community Support
      8 years ago

      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