How to deduct time in the previous row in the same column

[Anonymous]

Any idea, how to deduct time in previous row in the same column?

So, eg "row1-row2 = diff1", and etc... for whole column?

[v-kelly-msft]

Hi @Anonymous ，

Go to "edit queries">"Add column">"Index Column">"From 1":

Then go back to data view>"new column">create 2 columns using dax expressions as below:

Column =
IF (
    CALCULATE (
        MAX ( 'Table'[Time] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 )
    )
        = BLANK (),
    BLANK (),
    CALCULATE (
        MAX ( 'Table'[Time] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 )
    )
)

Time diff =
VAR a =
    DATE ( YEAR ( 'Table'[Time] ), MONTH ( 'Table'[Time] ), DAY ( 'Table'[Time] ) )
VAR b =
    DATE ( YEAR ( 'Table'[Column] ), MONTH ( 'Table'[Column] ), DAY ( 'Table'[Column] ) )
VAR c =
    IF ( b = BLANK (), 0, ABS ( VALUE ( a ) - VALUE ( b ) ) )
VAR a1 =
    HOUR ( 'Table'[Time] ) * 3600
        + MINUTE ( 'Table'[Time] ) * 60
        + SECOND ( 'Table'[Time] )
VAR b1 =
    HOUR ( 'Table'[Column] ) * 3600
        + MINUTE ( 'Table'[Column] ) * 60
        + SECOND ( 'Table'[Column] )
RETURN
    IF ( b = BLANK (), 0, c * 24 * 3600 + ABS ( VALUE ( b1 ) - VALUE ( a1 ) ) )

And you will see :

Here,the time diff was caculated in seconds,if you hope in hour or minute ,pls make a change per your need.

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

[amitchandak]

Is this time is descending order or can be assumed to ordered in desc order.  Or do you have any primary key?

Try both solutions as new columns. In case you have primary use that in place of rank.

Ranktime = RANKX(table,table[starttime],,Desc)
diff = datediff(table[starttime],maxx(filter(table,table[Ranktime]=EARLIER(table[Ranktime])-1),table[starttime]),MINUTE)

or
diff = datediff(table[starttime],minx(filter(table,table[starttime]>EARLIER(table[starttime])-1),table[starttime]),MINUTE)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

I like your second formula @amitchandak 

diff = datediff(table[starttime],minx(filter(table,table[starttime]>EARLIER(table[starttime])-1),table[starttime]),MINUTE)

however it gives me error message:

function MINX has to many arguments, max is 2

Any advise? 

[amitchandak]

Can you paste the formula you created? This seems correct. Also it is column

[Anonymous]

here we go @amitchandak 

Sloupec = datediff(CMA_RH[Column1.P1STARTTIME];minx(filter(CMA_RH;CMA_RH[Column1.P1STARTTIME]>EARLIER(CMA_RH[Column1.P1STARTTIME])-1);CMA_RH[Column1.P1STARTTIME];MINUTE))

[amitchandak]

Try

Sloupec = datediff(CMA_RH[Column1.P1STARTTIME];minx(filter(CMA_RH;CMA_RH[Column1.P1STARTTIME]>(EARLIER(CMA_RH[Column1.P1STARTTIME])-1));CMA_RH[Column1.P1STARTTIME]);MINUTE)

[Anonymous]

@amitchandak formula works, However it doesn't give me good results.

my request:

5:33:50 - 5:22:00 = 110sec

This is wrong BI calculation

[amitchandak]

Remove -1 from formula

Sloupec = datediff(CMA_RH[Column1.P1STARTTIME];minx(filter(CMA_RH;CMA_RH[Column1.P1STARTTIME]>(EARLIER(CMA_RH[Column1.P1STARTTIME])));CMA_RH[Column1.P1STARTTIME]);MINUTE)

if still wrong , check what values you are getting for. Is it same as expcted

New column =minx(filter(CMA_RH;CMA_RH[Column1.P1STARTTIME]>(EARLIER(CMA_RH[Column1.P1STARTTIME])));CMA_RH[Column1.P1STARTTIME])

[Anonymous]

Still getting wrong difference

Sloupec = datediff(CMA_RH[Column1.P1STARTTIME];MINX(filter(CMA_RH;CMA_RH[Column1.P1STARTTIME]>(EARLIER(CMA_RH[Column1.P1STARTTIME])));CMA_RH[Column1.P1STARTTIME]);SECOND)

and for 

Sloupec 2 = minx(filter(CMA_RH;CMA_RH[Column1.P1STARTTIME]>(EARLIER(CMA_RH[Column1.P1STARTTIME])));CMA_RH[Column1.P1STARTTIME])

[Anonymous]

Any other suggestions?

[amitchandak]

Please share some sample Data.

[Anonymous]

Test file saved here

https://uloz.to/file/Srckcyjh6NmZ/test-pbix 

[amitchandak]

Check, diff in seconds

https://www.dropbox.com/s/qqs3aovbo5wwwce/test%282%29.pbix?dl=0

Tried in two ways

[amitchandak]

Check, diff in seconds

https://www.dropbox.com/s/qqs3aovbo5wwwce/test%282%29.pbix?dl=0

[Anonymous]

you need to create one index column or you van refer below post

https://community.powerbi.com/t5/DAX-Commands-and-Tips/how-to-check-if-current-value-more-or-less-th...

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

check my blog here
https://community.powerbi.com/t5/Community-Blog/Connecting-to-a-Tabular-Model-Using-Power-BI/ba-p/91...