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.