Calculated column making reference to another row

[Anonymous]

Hello i need help with the next issue: I need to calculate the difference between 2 dates in the subsequent row if two conditions are achieved the number one is the column [Evento] = "BAJA-DEFINITIVA" and the in the column [Plaza Unica] the next row has the same ID, can i do this in M laguage or DAX?

Sample data:

Plaza Unica	Evento	Fecha del evento
5330111223	ALTA	07/03/2019
5330111223	BAJA - DEFINITIVA	30/04/2019
5330111223	ALTA	08/07/2019
5330111223	BAJA - DEFINITIVA	13/07/2019
5330111223	ALTA	08/08/2019
5330111223	BAJA	09/08/2019
5330111223	REINGRESO	12/08/2019
5330111230	BAJA	04/02/2019
5330111230	REINGRESO	04/02/2019
5330111230	BAJA	05/02/2019
5330111230	ALTA	11/03/2019
5330111230	BAJA - DEFINITIVA	03/09/2019
5330111230	ALTA	19/11/2019
5330111230	BAJA	20/11/2019
5330111252	ALTA	07/03/2019
5330111252	BAJA - DEFINITIVA	11/05/2019

[Mariusz · Accepted solution]

HI @Anonymous 

Sure, try something like this.

Column = 
IF( 
    'Table'[Evento] = "BAJA - DEFINITIVA", 
    VAR __date = 'Table'[Fecha del evento] 
    VAR __nextDate =
        CALCULATE(
            MIN( 'Table'[Fecha del evento]  ),
            ALLEXCEPT( 'Table', 'Table'[Plaza Unica] ),
            'Table'[Fecha del evento] > __date
        )
    RETURN 
    IF( NOT ISBLANK( __nextDate ), __nextDate - __date )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[AnthonyTilley · Accepted solution]

In M add a index column 

you will give you a row number that can be used to help create the calulated colunm

next go back to your main report and crete a calulated colunm 

Column = 
-- get the row number for the current row
var I = 'Table'[Index]
-- get the evento for the current row
var e = 'Table'[Evento]
--get the plaza unica for the current row
var p = 'Table'[Plaza Unica]
-- get the next row number
var i2 = I+1
-- get the plaza unica for the next row
var P2 = CALCULATE(Max('Table'[Plaza Unica]),all('Table'),'Table'[Index] = i2)
--get the current date
var d = 'Table'[Fecha del evento]
-- get date of next row 
var d2 = CALCULATE(Max('Table'[Fecha del evento]),all('Table'),'Table'[Index] = i2)
--calculate no of days between the two dates in dates
var DD = DATEDIFF(d,d2,DAY)
-- check if the value in evento is BAJA 
var check = IF(e = "BAJA - DEFINITIVA",TRUE,FALSE)
-- check if the next row is the same plaza unica
var check2 = if(p = p2, TRUE,FALSE)
-- check if both are true
Var F_Check = if(AND(check,check2),TRUE,FALSE)

--calculate the return value
Var ret = if(F_Check,DD,blank())

--REturn the value
Return ret

Example below

as you can see above this colunm should meet your criteria 

only if = BAJA - DEFINITIVA

the last entry does not calculate becasue there is no follwoing row 

calcualtes the number of days between the date in the current row and the date in the next row if criteria is met

hope this helps

[Mariusz · Accepted solution]

HI @Anonymous 

Sure, try something like this.

Column = 
IF( 
    'Table'[Evento] = "BAJA - DEFINITIVA", 
    VAR __date = 'Table'[Fecha del evento] 
    VAR __nextDate =
        CALCULATE(
            MIN( 'Table'[Fecha del evento]  ),
            ALLEXCEPT( 'Table', 'Table'[Plaza Unica] ),
            'Table'[Fecha del evento] > __date
        )
    RETURN 
    IF( NOT ISBLANK( __nextDate ), __nextDate - __date )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[amitchandak]

Try

diff =
datediff(table[Fecha del evento],minx(filter(table,table[Plaza Unica] =earlier(table[Plaza Unica]) && table[Fecha del evento] > earlier(table[Fecha del evento])
&& table[Evento] = "BAJA-DEFINITIVA"),table[Fecha del evento]),DAY)

depending on the need table[Evento] = "BAJA-DEFINITIVA"  can be earlier(table[Evento] ) = "BAJA-DEFINITIVA"

or table[Evento] = "BAJA-DEFINITIVA"  &&  earlier(table[Evento] ) = "BAJA-DEFINITIVA"

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 -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601