la creación de una medida que agrega datos faltantes implícitos

[CL7777]

Estoy luchando para crear una medida que sume el producto del costo de EOM * EOM QOH en la tabla (adjunta a continuación) incluyendo las fechas de fin de mes que no están presentes. Tengo varios números de pieza en una lista con todas las fechas de fin de mes donde se produjeron las transacciones. Para los meses en los que no se produjo ninguna transacción, todavía tenemos una cantidad disponible (QOH) y el costo del mes anterior que debe incluirse en la medida. Por ejemplo, en la tabla siguiente, la medida para el número de pieza A debe incluir los meses en los que faltan los datos, es decir, para la parte A, la medida debe ser igual:

8* 84 (ene 2018) + 7*84 (febrero de 2018) + 7* 84 (sin valor para marzo de 2018, así que utilice el valor prev non zero de febrero) + 7*84 (sin valor para abril de 2018, así que utilice el valor no cero anterior de febrero) + 7* 84 (sin valor (sin valor para abril de 2018, así que utilice el valor no cero anterior de febrero) + 7* 84 (sin valor para mayo 2018 así que utilice el valor no cero anterior para Feb) + 6*84 (jun 2018) + 5*84 (jul 2018) + 5*84 (sin valor para Aug 2018 así que use el valor no cero anterior para Jul) + 4*84 (septiembre 2018) + ...... etcetera...

Quiero hacer esto para cada número de pieza.. por lo que el número de pieza B tendría el mismo tipo de cálculo asociado a él.

Cualquier ayuda sería muy apreciada,

Datatable

Fin de mes	Número de pieza	EOM QOH	Costo de EOM
1/31/2018	A	8	$84
2/28/2018	A	7	$84
6/30/2018	A	6	$84
7/31/2018	A	5	$84
9/30/2018	A	4	$84
3/31/2019	A	8	$84
4/30/2019	A	7	$84
7/31/2019	A	4	$84
9/30/2019	A	3	$84
11/30/2019	A	2	$90
3/31/2018	B	22	$68
4/30/2018	B	20	$68
5/31/2018	B	17	$68
6/30/2018	B	15	$68
7/31/2018	B	13	$68
9/30/2018	B	10	$68
11/30/2018	B	7	$68
2/28/2019	B	16	$68
3/31/2019	B	15	$68
6/30/2019	B	21	$68
7/31/2019	B	19	$68
8/31/2019	B	16	$68
9/30/2019	B	12	$68
12/31/2019	B	22	$70

[Ashish_Mathur]

Hola

Puede descargar mi archivo PBI desde aquí.

Espero que esto ayude.

[CL7777]

Muchas gracias, abrí el archivo pbix pero la tabla creada parece correcta, pero no hay ninguna medida en el pbix que calcula lo que estoy buscando. ¿Me estoy perdiendo algo?

[AllisonKennedy]

Gracias @Ashish_Mathur por el archivo de ejemplo.

@CL7777

Pruebe esta medida:

LastNonBlank ?

VAR _ThisDate á MAX('Calendario'[Fecha])

VAR _LastData - MAXX(FILTER(ALL(Data[End of month ]), Data[End of month ]<-_ThisDate), Data[End of month ])

devolución

CALCULATE(LASTNONBLANKVALUE('Calendar'[Date],SUMX(Data, Data[EOM Cost]*Data[EOM QOH])), ALL('Calendar'[Date]),'Calendar'[Date]<-_ThisDate)

Para ver la solución desde @Ashish_Mathur tendrá que consultar el editor de Power Query, así que haga clic en Transformar datos en la pestaña Inicio de la cinta de opciones. Ha creado una nueva tabla con una copia de los 'Datos' para que haya una fila para cada mes. Dependiendo de la cantidad de tamaño de la tabla "Datos", esto podría ralentizar la actualización, pero debería hacer que el filtrado en la vista de informe sea más rápido.

He subido el archivo con mis ediciones debajo de mi firma aquí.

[AllisonKennedy]

@CL7777

De lo contrario, para hacer esto con una Medida, deberá crear la tabla como una visualización:

Costo total del producto: SUMX(Datos, Datos[Coste EOM]*Datos[EOM QOH])

LastNonBlank ?

VAR _ThisDate á MAX('Calendario'[Fecha])

VAR _LastData - MAXX(FILTER(ALL(Data[End of month ]), Data[End of month ]<-_ThisDate), Data[End of month ])

devolución

CALCULATE(LASTNONBLANKVALUE('Calendar'[Date],SUMX(Data, Data[EOM Cost]*Data[EOM QOH])), ALL('Calendar'[Date]),'Calendar'[Date]<-_ThisDate)

[CL7777]

Agradezco toda su ayuda, no puedo usar la consulta de energía para crear la tabla porque mi conjunto de datos se crea en Dax en BI mediante una instrucción summarize y es millones de líneas. pero estoy casi allí, una última cosa con la que necesito ayuda para resolver esto. He creado una tabla en Dax que tiene todas las fechas y datos allí. se ve así ahora (ver tabla de muestra a continuación)

Intenté usar su instrucción de medida de valor lastnonblank para crear el valor total (QOH * Cost) para la última fila no en blanco de la tabla siguiente (tabla llamada "tabla temporal"). No estoy obteniendo valores donde el QOH y el costo está en blanco, en otras palabras, es no recuperar el último valor no en blanco de estos y multiplicarlos juntos. Esto es lo que estoy usando para la medida, ¿qué estoy haciendo mal?

Medida 2 - LASTNONBLANKvalue('tabla temporal'[Último día del mes],SUMX('tabla temporal', 'tabla temporal'[Coste]*'tabla temporal'[QOH]))

El último día del mes	Número de pieza	Planta	QOH	Costo
10/31/2020	A	MfgSys
9/30/2020	A	MfgSys
8/31/2020	A	MfgSys
7/31/2020	A	MfgSys
6/30/2020	A	MfgSys
5/31/2020	A	MfgSys
4/30/2020	A	MfgSys
3/31/2020	A	MfgSys
2/29/2020	A	MfgSys
1/31/2020	A	MfgSys
12/31/2019	A	MfgSys	71	4.37
11/30/2019	A	MfgSys
10/31/2019	A	MfgSys	74	4.43
9/30/2019	A	MfgSys
8/31/2019	A	MfgSys
7/31/2019	A	MfgSys	76	4.43
6/30/2019	A	MfgSys	82	4.43
5/31/2019	A	MfgSys	86	4.43
4/30/2019	A	MfgSys
3/31/2019	A	MfgSys
2/28/2019	A	MfgSys	98	4.43
1/31/2019	A	MfgSys
10/31/2020	B	MfgSys
9/30/2020	B	MfgSys
8/31/2020	B	MfgSys	18	222.12
7/31/2020	B	MfgSys	19	222.12
6/30/2020	B	MfgSys
5/31/2020	B	MfgSys	20	222.12
4/30/2020	B	MfgSys
3/31/2020	B	MfgSys	14	222.12
2/29/2020	B	MfgSys
1/31/2020	B	MfgSys	4	222.12
12/31/2019	B	MfgSys
11/30/2019	B	MfgSys	7	242.40
10/31/2019	B	MfgSys	8	242.40
9/30/2019	B	MfgSys
8/31/2019	B	MfgSys
7/31/2019	B	MfgSys	5	242.40
6/30/2019	B	MfgSys	7	242.40
5/31/2019	B	MfgSys	15	242.40
4/30/2019	B	MfgSys	8	242.40
3/31/2019	B	MfgSys
2/28/2019	B	MfgSys	12	242.40
1/31/2019	B	MfgSys	13	242.40

[v-yiruan-msft · Accepted solution]

Hola @CL7777 ,

Puede crear una medida como se indica a continuación:

Measure = 
VAR _curdate =
    MAX ( 'temp table'[Last Day of Month] )
VAR _curpart =
    MAX ( 'temp table'[Part Number] )
VAR _predate =
    CALCULATE (
        MAX ( 'temp table'[Last Day of Month] ),
        FILTER (
            ALL ( 'temp table' ),
            'temp table'[Part Number] = _curpart
                && 'temp table'[Last Day of Month] < _curdate
                && NOT ( ISBLANK ( 'temp table'[QOH] ) )
                && NOT ( ISBLANK ( 'temp table'[Cost] ) )
        )
    )
VAR _prevalue =
    CALCULATE (
        MAX ( 'temp table'[QOH] ) * MAX ( 'temp table'[Cost] ),
        FILTER (
            ALL ( 'temp table' ),
            'temp table'[Part Number] = _curpart
                && 'temp table'[Last Day of Month] = _predate
        )
    )
RETURN
    IF (
        ISBLANK ( MAX ( 'temp table'[Cost] ) ) && ISBLANK ( MAX ( 'temp table'[QOH] ) ),
        _prevalue,
        MAX ( 'temp table'[QOH] ) * MAX ( 'temp table'[Cost] )
    )

Saludos

Rena

[CL7777]

¡Muchas gracias, eso es exactamente lo que estaba buscando ! muy apreciado

[Ashish_Mathur]

De nada, Allison.

[Ashish_Mathur]

Hola

No estoy seguro de a quién está respondiendo. En el enlace de descarga de archivos que compartí, hay una medida defenitely. Por favor, vuelva a comprobarlo.

[CL7777]

Le agradezco mucho su respuesta. No veo una medida que calcule lo que necesito. Veo una tabla llamada "todos los meses para cada parte" que tiene lo que necesito, pero no veo cómo se calculó esa tabla. La única medida que muestro es para una variable llamada valor de inventario que simplemente multiplica el costo de EOM por EOM QOH.. La tabla es exactamente lo que quiero, pero no veo el código que creó esa tabla.

[Ashish_Mathur]

Hola

¿@AllisonKennedy ha respondido a tu pregunta? He utilizado el Editor de consultas para transformar los datos.

[AllisonKennedy]

Puede encontrar el código de @Ashish_Mathur en Power Query Editor, haga clic en Advanced Editor en Power Query:

Dejar
Fuente: Datos,
"Columnas eliminadas" - Table.RemoveColumns(Source,"EOM QOH", "EOM Cost")),
"Filas agrupadas" - Table.Group(-"Columnas eliminadas", "Número de pieza", "Fecha de inicio", cada List.Min([-"Fin del mes "]), escriba nullable date, "Fecha de finalización", cada List.Max([-"Fin del mes "]), escriba nullable date-),
"Se calcula el inicio del mes" - Table.TransformColumns("Filas agrupadas","Fecha de inicio", Fecha.StartOfMonth, tipo fecha),
"Añadido Personalizado" - Table.AddColumn(-"Calculated Start of Month", "Month Span", cada uno (12 * (Date.Year([End date]) - Date.Year([Start date])))
+ (Date.Month([Fecha de finalización]) - Date.Month([Fecha de inicio]))
+ (si Date.Day([Fecha de finalización]) < Date.Day([Fecha de inicio])
entonces -1
else 0
)
+ 1),
"Añadido Custom1" - Table.AddColumn("Added Custom", "Month List", each List.Numbers(
1,
[Intervalo de mes]
)),
"Lista de mes ampliada" - Table.ExpandListColumn(-"Added Custom1", "Month List"),
"Añadido Custom2" - Table.AddColumn("Lista de mes expandido", "Fecha de finalización del mes", cada Fecha.EndOfMonth(
Date.AddMonths(
[Fecha de finalización],
0 - [Intervalo de mes] + [Lista de meses]
)
)),
"Columnas eliminadas1" - Table.RemoveColumns(-"Added Custom2","Fecha de inicio", "Fecha de finalización", "Intervalo de mes", "Lista de mes")
"Tipo de cambio" - Table.TransformColumnTypes(-"Columnas eliminadas1","Fecha de finalización del mes", fecha de tipo),
"Consultas combinadas" - Table.NestedJoin('Changed Type", ''Part Number"', 'Month End Date'', '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
"Datos expandidos" - Table.ExpandTableColumn(''Consultas combinadas', 'Datos"', 'EOM QOH'", "Coste EOM"', 'EOM QOH', "Coste EOM"'),
"Filas ordenadas" - Table.Sort("Datos expandidos","Número de pieza", Orden.Ascending, "Fecha de finalización del mes", Order.Ascending),
"Relleno" - Table.FillDown("Filas ordenadas", "EOM QOH", "Coste EOM" )
En
"Relleno"

[AllisonKennedy]

¿Tienes una tabla de citas?

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Esto le ayudará a darle todas las fechas que no están presentes y puede calcular la medida sobre la tabla de fechas y usar la fecha[mes] en la matriz para obtener el resultado que desea. A continuación, puede utilizar LASTNONBLANKVALUE para ayudar a obtener el resultado que desea.

[CL7777]

Gracias por su respuesta. Tengo una tabla de fechas, pero todavía no tengo claro cómo utilizo la última función no en blanco para crear mis resultados deseados. ¿podrías darme una medida de muestra que pudiera probar?