I calculate with the previous line

[renanpinheiro]

Hello,
I have the following table:

I need to do a calculation, decreasing the date of one line by the date of another line (from the second by the first, the third by the second, and so on ...).

Can someone help me?

[v-shex-msft · Accepted solution]

Hi @renanpinheiro,

You can try to use dax formula to achieve your requirement:

Measure:

Date Diff =
var currDate=max(DIM_SOLICITANTE[DATA_ALTERACAO])
var temp=MAXX(FILTER(ALL(DIM_SOLICITANTE),[DATA_ALTERACAO]<currDate),[DATA_ALTERACAO])
var prevDate=ifif(prevDate=Blank(),currDate,prevDate)
Return
DATEDIFF(prevDate,currDate,Minute)

Regards,

Xiaoxin Sheng

[renanpinheiro]

Hi @v-shex-msft,
The third variable you created "prevDate" is wrong. I can not do the IF you said.
I can not play the same variable inside the IF.

[v-shex-msft]

Hi @renanpinheiro,

The formula should be like this: (sorry for dupliated "if" typing)

Date Diff =
var currDate=max(DIM_SOLICITANTE[DATA_ALTERACAO])
var temp=MAXX(FILTER(ALL(DIM_SOLICITANTE),[DATA_ALTERACAO]<currDate),[DATA_ALTERACAO])
var prevDate=if(prevDate=Blank(),currDate,prevDate)
Return
DATEDIFF(prevDate,currDate,Minute)

Regards,

Xiaoxin Sheng

[MFelix]

hI @renanpinheiro,

First of all add an index column to you data, this will help with further calculations thenadd a custom column with the formula below:

if 
[Index] = 0 then 0 else 
if Index{[Index]-1}[WORKORDERID] = [WORKORDERID] then 
[DATA_ALTERACAO]- Index{[Index]-1}[DATA_ALTERACAO]
else 0

Then format the resulting column as duration.

Making the breakdown:

Index

Name of the step you want to get the numbers from (I named the Index column step with this name)

[Index]-1}

[Index] - Field index that allow to do a subtation to get the number we want so [Index]-1  gives us previous row

if [Index]= 0 then 0

Return 0 in first row instead of error

if Index{[Index]-1}[WORKORDERID] = [WORKORDERID] then

 This allows you to have this calculation made for several workorders so once the workorder changes it goes back to 0.

To be aware you need to have the table sorted by WorkorderId and then by Data_alteracao

Regards,

MFelix