Time Difference between Two Rows or Two Columns

[nerokasai]

Hi all,

I need your help to create a column with returns the # of Months acculumated based on when an employee starts/returns from work. 

Here's a sample table, where the last column "Months Accumulated" is the resulting column which I want to create:

Company Branch	Employee	Entry Date	Contract Start Date	Months Accumulated
A	Bob	2019-12-01	2017-10-01	17
A	Bob	2018-08-02	2017-10-01	10
A	Jack	2019-12-01	2018-05-01	19
B	Hannah	2019-12-01	2017-01-01	12
B	Hannah	2018-12-01	2017-01-01	24

ased on this, there are two conditions that would affect how the column "Months Accumulated" can be calculated:

- If Entry Date = Contract Start Date -> Entry Date - Contract Start Date
- If there's an existing entry record taking place before the considered row/entry date -> Considered Entry Date - Previous closest Entry Date

How best should I do this? Please help! 

Thank you!

[v-frfei-msft · Accepted solution]

Hi @nerokasai ,

1. Insert an index column in power query.

2. Create the calculated column as below.

Column = 
VAR ind = 'Table'[Index] + 1
VAR currecntemp = 'Table'[Employee]
VAR next =
    CALCULATE (
        MAX ( 'Table'[Employee] ),
        FILTER (
            'Table',
            'Table'[Index] = ind
                && 'Table'[Company Branch] = EARLIER ( 'Table'[Company Branch] )
        )
    )
VAR nextentrydate =
    CALCULATE (
        MAX ( 'Table'[Entry Date] ),
        FILTER (
            'Table',
            'Table'[Index] = ind
                && 'Table'[Company Branch] = EARLIER ( 'Table'[Company Branch] )
        )
    )
RETURN
    IF (
        currecntemp = next,
        DATEDIFF ( nextentrydate, 'Table'[Entry Date], MONTH ) + 1,
        DATEDIFF ( 'Table'[Contract Start Date], 'Table'[Entry Date], MONTH )
    )

For more details, please check the pbix as attached.

[FrankAT]

Hi @nerokasai 

create the calculated column like this:

DateDiff = DATEDIFF('Table'[Contract Start Date];'Table'[Entry Date];MONTH)

Regards FrankAT

[Greg_Deckler]

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...