Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Calculate difference of dates in rows

I have a table that consists of many job numbers, activity name, and dates. Every job is listed twice because we are only looking at two activity names: Final Closing Walkthrough & Foundation - Concrete. I need to calculate the days from Base End Date (Concrete) to Base End Date (Closing). This is an example of one specific job

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

     you want to do in PowerQuery, look at Pivot and make Job 1-110-110 one row with ActivityName values used as a new column like (Final Closing Walkthru and Foundation - Concrete as column names as well as Base End Date and Act End Date. Then you can do a DateDiff between the date columns

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Pivot job creates every job as a new column. Pivoting other columns makes the values 1 or 0. I might be misinterpreting your response

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I was able to get my solution goign a different route. I did 

    Base DIP = DATEDIFF(CALCULATE(MAX('Concrete - Closing DIP'[BaseEndDate]),'Concrete - Closing DIP'[ActivityName]="Foundation - Concrete"),CALCULATE(MAX('Concrete - Closing DIP'[BaseEndDate]),'Concrete - Closing DIP'[ActivityName]="Final Closing Walkthrough"),DAY)
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi, Anonymous 

      Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

       

      Best Regards

      Yongkang Hua