Obtención de totales mensuales de datos YTD

[stephanie116]

Recibo un informe mensual que muestra los totales de YTD para algunas categorías diferentes. Estoy tratando de encontrar una solución dentro de Power Query para convertir estos totales YTD en totales mensuales. ¿Alguna idea?

Datos sin procesar: Informes mensuales

INFORME DE ENERO

Categoría A	50
Categoría B	100
Categoría X	0

INFORME DE FEBRERO

Categoría A	125
Categoría B	197
Categoría X	15

INFORME DE MARZO

Categoría A	185
Categoría B	251
Categoría X	39

Conjunto de datos actual:

Mes	Categoría	YTD TOTAL
Enero	Categoría A	50
Enero	Categoría B	100
Enero	Categoría X	0
Febrero	Categoría A	125
Febrero	Categoría B	197
Febrero	Categoría X	15
Marzo	Categoría A	185
Marzo	Categoría B	251
Marzo	Categoría X	39

Conjunto de datos de destino:

Mes	Categoría	TOTAL MENSUAL
Enero	Categoría A	50
Enero	Categoría B	100
Enero	Categoría X	0
Febrero	Categoría A	75
Febrero	Categoría B	97
Febrero	Categoría X	15
Marzo	Categoría A	60
Marzo	Categoría B	54
Marzo	Categoría X	24

[edhans]

@stephanie116 ver si esto funciona. Esto es lo que hice - como se me pidió - en Power Query

1. Creé una tabla de fechas ad hoc para darme fechas, luego agregué los nombres de los números de mes y mes a esa tabla.
2. En la tabla, me fusioné con mi tabla de fechas por la columna Nombre del mes. Luego expandí el número de Mes y ordené por mes. Ahora puedo asegurarme de que los datos están correctamente ordenados para los siguientes pasos. Se ve así:
   1. 
3. Luego agregué una columna Index
4. Luego agregué la tabla de nuevo en sí misma como una tabla anidada simplemente haciendo referencia al paso anterior. Así que ahora cada registro de la tabla tiene una versión anidada de sí mismo. Nota: No sé cómo funcionará esto en conjuntos de registros muy grandes, pero sus datos no parecen estar en millones de registros.
   1. 
5. A continuación, filtro esa tabla anidada para incluir solo elementos en la categoría actual donde el índice es menor o igual que el índice actual.
   1. 
   2. Es assigining a varIndex y varCategory el número de índice y la categoría de la tabla principal, a continuación, hace un filtro (Table.SelectRows) en la tabla anidada. La forma en que he asignado variables es similar a cómo funcionan las variables en DAX, o a la función EARLIER() en DAX si sabes cómo funciona.
6. Finalmente agrego un List.Sum para totalizar la columna [Total] que todavía se muestra en mi tabla ahora filtrada.
7. Deshazte de todas las columnas innecesarias.

Resultado final:

Puede ver mi archivo PBIX aquí si desea jugar con él.

EDITAR: Lo mejoré un poco anidando solo los datos necesarios y haciendo el cálculo total (List.Sum()) en un solo setp. El paso inmediatamente después de agregar el índice es ahora:

= Table.AddColumn(#"Added Index", "Cumulative YTD Total", 
each let varCategory = [Category], varIndex = [Index]
in
List.Sum(
     Table.SelectRows(#"Added Index", each [Category] = varCategory and [Index] <= varIndex)[Total]
), Int64.Type)

Para un conjunto de datos más pequeño (menos de 100.000 registros probablemente, tal vez más) esto debería funcionar bien. Les animo encarecidamente a leer @ImkeFlos enlaces y estudiarlos. Planeo profundizar en ellos más tarde hoy y ver si puedo mejorar esto aún más para mi propio uso en el futuro.

La consulta más larga original todavía está en el PBIX vinculado a llamado [Tabla (Original Long Way)] para seguir a lo largo, pero [Tabla] consolida una serie de pasos como se muestra arriba y extrae solo los datos necesarios para cada registro frente a todos los datos para cada registro y luego filtrando en pasos posteriores.

[ImkeF]

Hola

Me encontré con una pregunta como esta antes e hice una entrada de blog sobre la solución: https://www.thebiccountant.com/2018/08/31/unravel-cumulative-totals-to-their-initial-elements-in-pow...

@edhans solución tiene una ruta lógica muy agradable y transparente, pero el rendimiento podría mejorarse si la lógica se aplicara en el nivel de categoría, como he descrito aquí:

https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-power...

[amitchandak]

@stephanie116

Es necesario asignar al mes un número como 1,2, 3 4 o año mes 202001, 2020002, etc. Supongamos que la columna es Número de mes. A continuación, pruebe una nueva columna

TOTAL MENSUAL - table[YTD TOTAL] -maxx(filter(Table,table[Month No] <'s anterior(table[Month No]) && table[CATEGORY] ? anterior(table[CATEGORY])),table[YTD TOTAL])

[Greg_Deckler]

No sé sobre Power Query, pero escribí una solución en DAX:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Revenue-Reverse-YTD/m-p/373185#M111

@ImkeF podría saber cómo resolverlo en M.