Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hola expertos,
Supongamos que tengo una fecha por debajo con algún valor, así como el valor que falta.
Valor de fecha
8/18/2020---45
8/19/2020---36
8/20/2020---25
8/21/2020
8/22/2020
8/23/2020
8/24/2020---96
8/25/2020---22
8/26/2020---89
8/27/2020
8/28/2020---56
8/29/2020---85
Aquí nos faltan datos para la fecha 8/27/2020 (1 día) y desde la fecha 08/21 a 08/23 (3 días)
Quiero crear una medida de tal manera que pueda obtener un promedio de datos para la fecha que falta.
Por ejemplo:-(25+96)/2---de la fecha 08/21 a 08/23 y (89+56)/2--para la fecha 8/27
Gracias
Annu
Solved! Go to Solution.
Hola, @Annu_choubey , pls se refieren a una solución DAX.
Avg =
VAR __prev_nonblank =
LASTNONBLANKVALUE (
FILTER ( ALL ( DS[Date] ), DS[Date] <= MIN ( DS[Date] ) ),
MAX ( DS[Value] )
)
VAR __next_nonblank =
FIRSTNONBLANKVALUE (
FILTER ( ALL ( DS[Date] ), DS[Date] >= MIN ( DS[Date] ) ),
MAX ( DS[Value] )
)
RETURN
IF (
ISBLANK ( MIN ( DS[Value] ) ),
( __next_nonblank + __prev_nonblank ) / 2,
MIN ( DS[Value] )
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hola, @Annu_choubey , pls se refieren a una solución DAX.
Avg =
VAR __prev_nonblank =
LASTNONBLANKVALUE (
FILTER ( ALL ( DS[Date] ), DS[Date] <= MIN ( DS[Date] ) ),
MAX ( DS[Value] )
)
VAR __next_nonblank =
FIRSTNONBLANKVALUE (
FILTER ( ALL ( DS[Date] ), DS[Date] >= MIN ( DS[Date] ) ),
MAX ( DS[Value] )
)
RETURN
IF (
ISBLANK ( MIN ( DS[Value] ) ),
( __next_nonblank + __prev_nonblank ) / 2,
MIN ( DS[Value] )
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Gracias😊!!
Puede intentarlo entrando en Power Query
- Duplica tu columna Valor
- Rellenar la columna Valor Orignal por Hasta
- Rellenar columna de valor duplicado por abajo
- Añadir una columna personalizada (Nuevo valor) por ([Valor] +[Valor - Copiar])/2
- Esta nueva columna tendrá los resultados deseados.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
2 | |
2 | |
2 | |
2 | |
1 |