# Power BI Exchange

View Only

## Datediff in same column

• #### 1.  Datediff in same column

Posted Feb 01, 2021 11:14 AM
Edited by julien rouleau Feb 02, 2021 11:40 AM

Hi everyOne

I have tried several solutions to solve this problem.

See explanation in "comment" column and expected result in "Expected value" column :

I 've spent hours on this formula but this is not the result i expect.

Thanks

Expected value =
VAR MaxRank =
CALCULATE ( MAX ( DT[Item Rank] ), ALLEXCEPT ( DT, DT[NUMBEREDF] ) )
VAR temp =
TOPN (
1,
FILTER (
DT,
DT[NUMBEREDF] = EARLIER ( DT[NUMBEREDF] )
&& DT[DATEEDF] < EARLIER ( DT[DATEEDF] )
),
DT[NUMBEREDF], DESC
)
RETURN
IF (
DT[ID group Rank] = 1
&& DT[ID group Rank] <> MaxRank,
0,
IF (
DT[ID group Rank] = MaxRank,
DATEDIFF ( DT[Change date], DT[Close id date], DAY ),
DATEDIFF ( MAXX ( temp, DT[Change date] ), DT[Change date], DAY )
)
)

------------------------------
julien
------------------------------

• #### 2.  RE: Datediff in same column

Posted Jan 15, 2022 02:31 PM

Steps to populate differrence between two immediate rows in same column to another column(A2-A1)

step 1-create two index column from Add column section ie one starting with 0 with column name as Index and another index column starting with 1 with name Index.1

Step2-Add a new column with respective name and use the below formula and enter

var diff= Table column name-CALCULATE(SUM(Table column name),FILTER('tablename','tablename'[Index.1]=EARLIER('insights-logs-connection'[Index]))) return IF (diff=VALUE(Table column name),0,diff)

Table column name will be like 'table name'[column name] choose automaticaly from power BI list dropdwon

Table name will be like 'table name' choose automaticaly from power BI list dropdown

------------------------------
Jag kris
ceo
------------------------------