Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculated column making reference to another row

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 UnicaEventoFecha del evento
5330111223ALTA07/03/2019
5330111223BAJA - DEFINITIVA30/04/2019
5330111223ALTA08/07/2019
5330111223BAJA - DEFINITIVA13/07/2019
5330111223ALTA08/08/2019
5330111223BAJA09/08/2019
5330111223REINGRESO12/08/2019
5330111230BAJA04/02/2019
5330111230REINGRESO04/02/2019
5330111230BAJA05/02/2019
5330111230ALTA11/03/2019
5330111230BAJA - DEFINITIVA03/09/2019
5330111230ALTA19/11/2019
5330111230BAJA20/11/2019
5330111252ALTA07/03/2019
5330111252BAJA - DEFINITIVA11/05/2019
  • 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.
  • 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

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion

    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.
  • 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

  • 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-with-Winners/ba-p/890814
    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
    https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
    https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601