Forum Discussion
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 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 |
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 retExample 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
- MariuszCommunity 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. - AnthonyTilleySolution Sage
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 retExample 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
- amitchandakSuper User
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