Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hola
Necesito crear una columna calculada que resta el valor de fila actual del valor de fila anterior en función de la fecha y el nombre del cliente. Por ejemplo:
Fecha | Valor | Cliente | Calc_column (valor esperado) |
01-04-2020 | 100 | A | |
01-04-2020 | 400 | B | |
01-04-2020 | 500 | C | |
02-04-2020 | 120 | A | 20 |
02-04-2020 | 440 | B | 40 |
02-04-2020 | 560 | C | 60 |
¡Gracias de antemano!
Solved! Go to Solution.
Hola @ashrafkotb
Tratar
Calc_column =
var _thisDate = Table[Date]
var _prevDate = CALCULATE(MAX(Table[Date]), ALLEXCEPT(Table, Table[Customer]), Table[Date] < _thisDate )
RETURN
Table[Value] - CALCULATE(MAX(Table[Value]), ALLEXCEPT(Table, Table[Customer]), Table[Date] = _prevDate )
Vea mi artículo sobre el tiempo medio entre el error (MTBF) que utiliza EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Así que en tu caso, algo como:
Calc_column =
VAR __PreviousDate = MAXX(FILTER('Table',[Date] < EARLIER([Date]) && [Customer] = EARLIER([Customer])),[Date])
VAR __PreviousValue = MAXX(FILTER('Table',[Date] = __PreviousDate) && [Customer] = EARLIER([Customer])),[Date])
RETURN
[Value] - __PreviousValue
Obtener un error de sintaxis para esta fórmula.
Pruebe nuevas columnas como
Fecha máxima: maxx(filter(Table, table[Date]<earlier(Table[Date]) && table[Customer]-earlier(Table[Customer])),Table[Date])
diff - Table[value] - maxx(filter(Table, table[Date]-earlier(Table[max date]) && table[Customer]-earlier(Table[Customer])),Table[value])
Hola @ashrafkotb
Tratar
Calc_column =
var _thisDate = Table[Date]
var _prevDate = CALCULATE(MAX(Table[Date]), ALLEXCEPT(Table, Table[Customer]), Table[Date] < _thisDate )
RETURN
Table[Value] - CALCULATE(MAX(Table[Value]), ALLEXCEPT(Table, Table[Customer]), Table[Date] = _prevDate )
Esto funcionó, gracias.