Dificultades para resolver los totales de la matriz

[Syndicate_Admin]

Hola a todos

Sé que esta es una variación del infame problema de "Medir totales", pero incluso sabiendo así he estado luchando para resolverlo realmente.

Mi modelo de pronóstico está tratando de sumar ventas y pronósticos por año, mes y territorio. Para un mes determinado, si existen ventas, se debe utilizar el valor de venta. De lo contrario, se debe utilizar el valor "Pronóstico", donde "Pronóstico" se define como el valor de ventas del mismo mes del año anterior, multiplicado por una tasa de crecimiento definida por el usuario para el Territorio (recopilada utilizando segmentaciones de parámetros de qué pasaría si).

Aquí está el enlace a mi archivo de demostración (no datos reales, solo usando AdventureWorks para demostrar)
https://drive.google.com/file/d/1wwzIRNcMIoJavaV92_Jz-a602rzxFpJ1/view?usp=sharing

El problema con el que me encuentro es que mientras los totales mensuales se calculan correctamente, los subtotales del año no lo hacen. En cambio, el total del año simplemente está tirando del total del año anterior.

Estoy bastante seguro de que esto se debe a que mi medida se está calculando con la granularidad incorrecta en esas filas totales. Necesito que calcule la granularidad del mes, luego sume el resultado, pero me ha costado hacerlo.

Aquí está la medida gigante que se está utilizando:

Sales_Forecast by Group = 
VAR _EuropeRate = [EuropeRate Value] -- from what-if slicer
VAR _NorthAmericaRate = [North America Rate Value] -- from what-if slicer
VAR _PacificRate = [Pacific Rate Value] -- from what-if slicer

VAR _CurrentDate = MAX('Date'[Date])
VAR _MaxShipDate = CALCULATE(MAX(Sales[ShipDate]), ALL(Sales))
VAR _LastCompleteDate = DATE(YEAR(_MaxShipDate),MONTH(_MaxShipDate)-1,1)

VAR _ForecastTable = 
ADDCOLUMNS(
    SUMMARIZE(
        Territory,
        Territory[Group]
    ),
    "_L12M Sales",
    CALCULATE(
        [Sales],
        SAMEPERIODLASTYEAR('Date'[Date])
    )
)

VAR _ForecastTable2 = 
ADDCOLUMNS(
    _ForecastTable,
        "Sales_Forecast",
        SWITCH(
            Territory[Group],
            "Europe",
            IF(
                _CurrentDate < _LastCompleteDate,
                [Sales],
                [_L12M Sales] * (1 + _EuropeRate)
            ),
            "North America",
            IF(
                _CurrentDate < _LastCompleteDate,
                [Sales],
                [_L12M Sales] * (1 + _NorthAmericaRate)
            ),            
            "Pacific",
            IF(
                _CurrentDate < _LastCompleteDate,
                [Sales],
                [_L12M Sales] * (1 + _PacificRate)
            )
        )   
)
VAR _ForecastSum = SUMX(_ForecastTable2, [Sales_Forecast])

Return
_ForecastSum

Siento que el problema está en la variable _ForecastTable, donde estoy resumiendo solo por Grupo. Creo que de alguna manera también necesito incluir el mes en ese resumen.

Sin embargo, cuando intenté reemplazar con el código a continuación, no solo no calculó correctamente, sino que también impidió que se mostraran los meses futuros.

VAR _ForecastTable = 
ADDCOLUMNS(
    SUMMARIZE(
        Sales,
        Territory[Group],
        'Date'[YYYYMM]
    ),
    "_L12M Sales",
    CALCULATE(
        [Sales],
        SAMEPERIODLASTYEAR('Date'[Date])
    )
)

Vista del modelo:

Cualquier consejo o recomendación es apreciada.

¡Gracias!

[Syndicate_Admin · Accepted solution]

@ebeery Sin profundizar demasiado en su"medida gigante" como lo ha dicho, una solución rápida es agregar el contexto del mes además de lo que ya ha calculado, ya que el nivel del mes es correcto:

FixTotals =

SUMX(VALORES('Fecha'[Start_Of_Month]), [Sales_Forecast por Grupo])

[Syndicate_Admin]

¡Genio! ¡Muchas gracias @AllisonKennedy! Creo que el resto de mi medida probablemente todavía necesite algo de trabajo, pero esto hizo que devolvió los resultados esperados.

[Syndicate_Admin]

A nivel de mes, la siguiente condición está bien, pero fallará a nivel de año.

  _CurrentDate < _LastCompleteDate,

Así que en lugar de

VAR _CurrentDate = MAX('Date'[Date])

Crear dos variables

VAR _StartDate = MIN('Date'[Date])
VAR _EndDate = MAX('Date'[Date])

Luego cambie si la condición a algo como esto ...

If LastCompleteDate > StartDate && LastCompleteDate <EndDate Then
   Calculate Sales for the period from StartDate to LastCompleteDate + Calculate Forecast for the period from LastCompleteDate to EndDate
ElseIf LastCompleteDate > StartDate && LastCompleteDate > EndDate Then
   Calculate Sales
Else [or Elseif LastCompleteDate < StartDate && LastCompleteDate < EndDate)Then
  Calculate Forecast.

[Syndicate_Admin]

¡Gracias @sreenathv! Creo que entiendo este enfoque, simplemente no he podido implementarlo con éxito en la medida todavía. Voy a seguir trabajando en ello