Forum Discussion

ConnorH's avatar
ConnorH
Microsoft Employee
6 years ago
Solved

Finding days between two rows date values

This is kind of complex and I haven't been able to figure it out. I am trying to create a column that finds the number of days between a project status but the project ID must be the same. The projec...
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi ConnorH ,

     

    Create a Calculated Column

     

    Column =
VAR _prevstage_sameprojId_date =
    CALCULATE (
        MAX ( 'Table1'[Modified date] ),
        FILTER (
            'Table1',
            'Table1'[Modified date]
                < EARLIER ( 'Table1'[Modified date] )
                && Table1[Project ID]
                    = EARLIER ( Table1[Project ID] )
        )
    )
RETURN
    DATEDIFF (
        _prevstage_sameprojId_date,
        'Table1'[Modified date],
        DAY
    )

     

    Regards,
    Harsh Nathani

    Appreciate with a Kudos!! (Click the Thumbs Up Button)
    Did I answer your question? Mark my post as a solution!

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi ConnorH ,

 

Create a Calculated Column

 

Column =
VAR _prevstage_sameprojId_date =
    CALCULATE (
        MAX ( 'Table1'[Modified date] ),
        FILTER (
            'Table1',
            'Table1'[Modified date]
                < EARLIER ( 'Table1'[Modified date] )
                && Table1[Project ID]
                    = EARLIER ( Table1[Project ID] )
        )
    )
RETURN
    DATEDIFF (
        _prevstage_sameprojId_date,
        'Table1'[Modified date],
        DAY
    )

 

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

  • GeorgeR1's avatar
    GeorgeR1
    Frequent Visitor
    4 years ago

    I have a similar instance where I would really aprecaite your help.

     

    I am trying to create a column that finds the number of workingdays (excluding weekends and bankholidays - I have a list of all bank holidays in a table) between Milestones but the Audit Review ID must be the same. I will need to calculate the difference between the End Day of Milestone 1 with the end date with Milestone 2 and so on... Not all Audit Reviews have an End Date for all 7 milestones, some only have a few as seen in example.

     

    Audit Review IDMilestone TypeMilestone End Date
    1234561. Background Research01/12/2021
    1234562. Terms of Reference03/02/2022
    1234563. Opening Meeting04/03/2022
    1234564. Fieldwork and Closing Meeting21/03/2022
    1234565. Draft Report25/03/2022
    1234566. Management Responses29/03/2022
    1234567. Final Report07/04/2021
    2345671. Background Research01/01/2022
    2345672. Terms of Reference03/01/2022
    2345673. Opening Meeting04/02/2022
    2345674. Fieldwork and Closing Meeting12/02/2022
    3456781. Background Research01/01/2021
    3456782. Terms of Reference23/01/2022

     

    For example, I would expect a value of 5 for the difference between (4. Fieldwork & Closing Meeting) and (5. Draft Report) for Audit 123456.

     

    Would apprecaite your help.

    • lipsary's avatar
      lipsary
      New Member
      3 years ago

      GeorgeR1  : I have similiar kind of scenario. By any chance if you find any solution . Kindly, can you please post here.

  • young351's avatar
    young351
    Frequent Visitor
    3 years ago

    I used this to calculate Days Between Loads and was wondering how I could add to it to also calculate if it was the last Load, then how many days since then to "today"?