La media móvil que utiliza un índice no funciona

[Syndicate_Admin]

Conjunto de datos de frutas EDITAR: CONJUNTO DE DATOS SIN PROCESAR INCLUIDO Y RESULTADO DE EJEMPLO

Hola, estoy trabajando en una media móvil dinámica para los precios de la fruta. He escrito una medida que funciona correctamente en algunos casos, pero no en todos.

Por favor, ¿puede alguien ayudarme a solucionar esto?

La idea es que el usuario pueda seleccionar un número de días (Fecha de compra) para promediar durante los cuales se puede integrar en una rebanadora. Técnicamente quiero que sea una serie de puntos de datos en lugar de días (por lo que ignorará los fines de semana y los huecos bancarios donde no hay precios, por ejemplo).

Alguna complicación adicional: el promedio debe calcularse para cada fruta y para cada "Fecha de entrega", que debe poder agregarse de meses > trimestres > temporadas > años. La fecha de compra determina el período promedio, la fecha de entrega se puede ver como una categoría diferente.

He destacado un ejemplo en el que comienza a salir mal en comparación con el cálculo manual de esto en Excel.

El DAX está a continuación:

0.Moving Avg =

Dónde Current_Date = FIRSTNONBLANK('Datos de frutas'[Fecha de compra],1)

Dónde Number_Of_Days = SELECTEDVALUE('Promedio de días'[Promedio de días])

Dónde Offset_Date = filtro(

TODO('Calendario'[Fecha]),

RANKx(FILTRO(TODO('Calendario'),'Calendario'[Fecha]<(Current_Date-1)),'Calendario'[Fecha],,DESC)=Number_Of_Days)

DÓNDE avrg =

CALCULAR( PROMEDIO('Datos de frutas'[Precio]),

FILTRO ( TODO ('Datos de frutas'[Fecha de compra]), 'Datos de frutas'[Fecha de compra]< Current_Date && 'Datos de frutas'[Fecha de compra] > Offset_Date))

devolución avrg

[Syndicate_Admin]

Hola @ml voluntades,

En su siguiente fórmula, no debe usar "Current_Date-1", ya que la fecha de compra no es continua.

FILTER(ALL('Calendar'),'Calendar'[Date]<(Current_Date-1))

Aquí está mi solución.

1.La fórmula de la tabla Calendario.

Calendar = VALUES('Fruit Data'[Purchase Date])

2.La fórmula de la medida media móvil.

Moving Avg =
VAR Current_Date =
    MAX ( 'Fruit Data'[Purchase Date] )
VAR Number_Of_Days =
    SELECTEDVALUE ( 'Average Days'[Average Days] )
VAR Offset_Date =
    FILTER (
        ALL ( 'Calendar'[Date] ),
        RANKX (
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] < ( Current_Date ) ),
            'Calendar'[Date],
            ,
            DESC
        ) = Number_Of_Days
    )
VAR avrg =
    CALCULATE (
        AVERAGE ( 'Fruit Data'[Price] ),
        FILTER (
            ALL ( 'Fruit Data' ),
            'Fruit Data'[Delivery Date] = MAX ( 'Fruit Data'[Delivery Date] )
                && 'Fruit Data'[Product] = MAX ( 'Fruit Data'[Product] )
                && 'Fruit Data'[Purchase Date] < Current_Date
                && 'Fruit Data'[Purchase Date] >= Offset_Date
        )
    )
RETURN
    avrg

Obtén el resultado.

Adjunto mi muestra a continuación como referencia.

Saludos
Equipo de apoyo a la comunidad _ kalyj

Si esta publicación ayuda,entonces considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

[Syndicate_Admin]

Hola v-kalyj-msft muchas gracias por mirar mi problema, realmente aprecio la ayuda!

He probado esto en un ejemplo en el que estamos viendo uvas para la entrega FT 2023. Creo que el problema es que necesitamos que los promedios de fecha de entrega única se agreguen a un año fiscal de entrega.

He creado una nueva hoja aquí Prueba del modelo de precios de frutas que creo que demostró el problema y el resultado esperado si miras en la pestaña "Muestra de prueba de uvas". He capturado la salida que da su modelo en estas condiciones en comparación con los calcs de Excel.

[Syndicate_Admin · Accepted solution]

Hola @ml voluntades,

De acuerdo con su nueva muestra, aquí está mi solución.

Crea dos medidas.

Avg of Price = 
CALCULATE (
    AVERAGE ( 'Fruit Data'[Price] ),
    FILTER (
        ALL ( 'Fruit Data' ),
        'Fruit Data'[Purchase Date] = MAX ( 'Fruit Data'[Purchase Date] )
            && 'Fruit Data'[Product] = MAX ( 'Fruit Data'[Product] )
            && 'Fruit Data'[Delivery FY] = MAX ( 'Fruit Data'[Delivery FY] )
    )
)

Moving Avg = 
VAR Current_Date =
    MAX ( 'Fruit Data'[Purchase Date] )
VAR Number_Of_Days =
    SELECTEDVALUE ( 'Average Days'[Average Days] )
VAR Offset_Date =
    FILTER (
        ALL ( 'Calendar'[Date] ),
        RANKX (
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] < ( Current_Date ) ),
            'Calendar'[Date],
            ,
            DESC
        ) = Number_Of_Days
    )
VAR avrg =
    AVERAGEX (
        FILTER (
            ALL ( 'Fruit Data' ),
            'Fruit Data'[Delivery FY] = MAX ( 'Fruit Data'[Delivery FY] )
                && 'Fruit Data'[Product] = MAX ( 'Fruit Data'[Product] )
                && 'Fruit Data'[Purchase Date] < Current_Date
                && 'Fruit Data'[Purchase Date] >= Offset_Date
        ),
        [Avg of Price]
    )
RETURN
    avrg

Obtenga el resultado esperado.

Adjunto mi muestra a continuación como referencia.

Saludos
Equipo de apoyo a la comunidad _ kalyj

Si esta publicación ayuda,entonces considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

[Syndicate_Admin]

¡¡Muchas gracias!! Impresionante, finalmente funciona.

[Syndicate_Admin]

Proporcione datos de muestra desinfectados que cubran completamente su problema. Pegue los datos en una tabla de su publicación o use uno de los servicios de archivos. Por favor, muestre el resultado esperado.

[Syndicate_Admin]

Gracias por su ayuda aquí están los datos en bruto y un ejemplo de cómo quiero que funcione el cálculo:

Conjunto de datos de frutas

En términos de cómo me gustaría que se viera en PowerBI, necesitaría trazar los datos tanto en una tabla como en un gráfico donde para cada dato de compra y cada fecha de entrega habría un promedio móvil calculado dinámicamente que podría ser de 5,10,15,20 días (etc.). También debería poder agregar la fecha de entrega a un mes de entrega o año de entrega y el promedio aún debería calcularse. En el ejemplo de la hoja el plazo de entrega es verano 2024.

As long as the average calculates correctly in a table I can figure out the rest from there though.

Conjunto de datos de frutas