Add days to previous date to create a new start date

[jakenguyen02]

Hi everyone - I'm having trouble figuring this out and would appreciate the help. I have a speadsheet of audit tasks and how many days it takes to complete.  I want to generate a new start date for the next task by adding the days to complete to the original start date + 1 day. Please see the example below. 

Original 

Expected output

[Anonymous · Accepted solution]

Hi @jakenguyen02 ,

First add a index column in Powerquery:

And then please create a new calculated column:

New Start Date =
VAR __index = 'Table'[Index]
VAR __start_date =
    CALCULATE (
        MAX ( 'Table'[Start Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Preparer] )
    )
VAR __days =
    CALCULATE (
        SUM ( 'Table'[Days to Complete] ),
        ALLEXCEPT ( 'Table', 'Table'[Preparer] ),
        'Table'[Index] < __index
    )
        + CALCULATE (
            COUNTROWS ( 'Table' ),
            ALLEXCEPT ( 'Table', 'Table'[Preparer] ),
            'Table'[Index] < __index
        )
VAR __result = __start_date + __days
RETURN
    __result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[ryan_mayu · Accepted solution]

@jakenguyen02 

you can also try this to create a column

Column =

VAR _start=maxx(FILTER('Table','Table'[Preparer]=EARLIER('Table'[Preparer])&&not(ISBLANK('Table'[Start Date]))),'Table'[Start Date])

VAR _starttask=maxx(FILTER('Table','Table'[Preparer]=EARLIER('Table'[Preparer])&&not(ISBLANK('Table'[Start Date]))),'Table'[Task])

VAR _rows=CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Preparer]=EARLIER('Table'[Preparer])&&'Table'[Task]<EARLIER('Table'[Task])&&'Table'[Task]>=_starttask))

VAR _days=CALCULATE(sum('Table'[Days to Complete]),FILTER('Table','Table'[Preparer]=EARLIER('Table'[Preparer])&&'Table'[Task]<EARLIER('Table'[Task])&&'Table'[Task]>=_starttask))

return if(ISBLANK('Table'[Start Date]),_start+_days+_rows,'Table'[Start Date])

[jakenguyen02]

This also works perfectly. Thank you. 

[Anonymous · Accepted solution]

Hi @jakenguyen02 ,

First add a index column in Powerquery:

And then please create a new calculated column:

New Start Date =
VAR __index = 'Table'[Index]
VAR __start_date =
    CALCULATE (
        MAX ( 'Table'[Start Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Preparer] )
    )
VAR __days =
    CALCULATE (
        SUM ( 'Table'[Days to Complete] ),
        ALLEXCEPT ( 'Table', 'Table'[Preparer] ),
        'Table'[Index] < __index
    )
        + CALCULATE (
            COUNTROWS ( 'Table' ),
            ALLEXCEPT ( 'Table', 'Table'[Preparer] ),
            'Table'[Index] < __index
        )
VAR __result = __start_date + __days
RETURN
    __result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[jakenguyen02]

This is perfect. Thank you!!!!