Duplicar filas con fechas diferentes

[jacob2102]

Hola,

Tengo un conjunto de datos donde cargo los costes de un producto en una fecha concreta. Estos costes se mantienen a lo largo del tiempo hasta que exista una nueva fecha con coste para cada producto.

Fecha	Producto	Coste
21/08/2021	A	0,25
21/08/2021	B	0,28
21/08/2021	C	0,30

Esta tabla de costes se relaciona con la tabla calendario (fecha - fecha coste) y esta a su vez con la tabla de facturas (fecha - fecha factura). El objetivo es poder calcular el coste del producto para cada línea de venta según la fecha de la factura.

El problema es que el coste actual, con fecha 21/08/2021 es el mismo para todos los días posteriores hasta que exista un nuevo coste para el día 20/08/2021.

He generado una nueva consulta con una lista de fechas, fecha de inicio 21/08/2021 y fecha fin el día actual, para que se vaya actualizando con el paso del tiempo. Mi idea es combinar ambas consultas "tabla fechas y tabla coste comercial" y así poder tener el coste de cada producto en cada fecha concreta. La idea es conseguir una tabla parecida a la siguiente:

Fecha	Producto	Coste
21/08/2021	A	0,25
21/08/2021	B	0,28
21/08/2021	C	0,30
22/08/2021	A	0,25
22/08/2021	B	0,28
22/08/2021	C	0,30
23/08/2021	A	0,27
23/08/2021	B	0,31
23/08/2021	C	0,33

El problema viene cuando tras combinar consultas quiero completar el resto de días con los valores similares. No puedo utilizar la opción "rellenar" porque sólo ejecuta la acción con la última fila. ¿Cómo puedo ejecutar la acción para duplicar los valores en las fechas donde no hay ningún dato?

Gracias por la ayuda.

Un saludo,

[v-yalanwu-msft]

Hi, @jacob2102 ;

You could delete realationship and the create a measure as follows：

Coste2 = CALCULATE(MAX('Costes'[Coste]),FILTER(ALLEXCEPT(Costes,Costes[Producto]),[Fecha]<=MAX('Facturas'[Fecha])))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jacob2102]

Thanks for your answer, but I can't delete relationships and I need to insert this cost in the table "facturas" to calculate the margin of sales.

Thanks.

[PaulDBrown]

puedes compartir un archivo PBIX con datos de ejemplo? (A través de Onedrive, Dropbox o similar)

[jacob2102]

https://drive.google.com/file/d/1AT6KNA-nMM1CfYjisHBfLpKErJ__b8Cw/view?usp=sharing 

Hola,

Gracias por la respuesta.

Adjunto enlace con un ejemplo tipo, basado en una muestra tipo de los datos originales con los que quiero desarrollar el análisis.

El objetivo es poder calcular el coste asociado a cada línea de venta, en base a su fecha y cantidades, y así obtener los margenes de rentabilidad de las ventas.

Gracias. Un saludo,

[PaulDBrown]

con este modelo...

y esta medida...

Calculo Costes =
VAR PrevD =
    CALCULATE (
        MAX ( Calendario[Fecha] ),
        FILTER (
            ALL ( Calendario ),
            Calendario[Fecha] <= MAX ( Calendario[Fecha] )
                && NOT ( ISBLANK ( [Sum Coste] ) )
        )
    )
RETURN
    CALCULATE (
        [Sum Coste],
        FILTER ( ALL ( Calendario[Fecha] ), Calendario[Fecha] = PrevD )
    )

se obtiene...

Si luego añades...

Coste total =
VAR _table =
    ADDCOLUMNS (
        SUMMARIZE ( Facturas, 'Dim Producto'[Producto], Calendario[Fecha] ),
        "calc", [Sum Cantidad] * [Calculo Costes]
    )
RETURN
    SUMX ( _table, [calc] )

y...

Margen = [Suma Importe] - [Coste total]

llegas a...

Te adjunto el archivo PBIX 

[jacob2102]

Muchas gracias por la respuesta.

Con esta medida sí puedo obtener los costes para cada una de las fechas, asociando el coste según la fecha anterior.

El problema surge cuando quiero añadir este valor en la tabla de facturas, para asociar el coste a cada línea de producto y posteriormente poder agrupar por Código de Factura.
He generado una medida DAX que es:

Coste = IF(
         ISBLANK(CALCULATE(VALUES(Costes[Coste]),
         FILTER(Costes,Costes[Producto]=Facturas[Producto] && Costes[Fecha]=Facturas[Fecha]))),
            CALCULATE(VALUES(Costes[Coste]),
            FILTER(Costes,Costes[Producto]=Facturas[Producto] && Costes[Fecha<Facturas[Fecha])),
                CALCULATE(VALUES(Costes[Coste]),
                FILTER(Costes,Costes[Producto]=Facturas[Producto] && 
                Costes[Fecha]=Facturas[Fecha])))

El problema es que cuando existen más de una fecha anterior, por ejemplo si incluyo un coste para el 15/08/2021, devuelve un error al existir más fechas y no saber identificar cuál seleccionar.

¿Conocéis alguna alternativa para seleccionar la fecha anterior del día más cercano?

El objetivo es que para el día 22/08/2021 me devuelva los costes del día 21/08/2021, para el 25/08/2021 los del día 23/08/21 y para el 19/08/2021 los del día 15/08/2021.

He probado con opciones "EARLIER" y "EARLIEST" pero me devuelve un error.

Gracias. Un saludo,

[PaulDBrown]

Con la estructura + medidas que te he incluido en el archivo (+ una nueva para motrar el CosteProducto) ya puedes hacer los cálculos que necesitas no?

Si lo que quieres es incluir los costes en la misma tabla de facturas, puedes añadir una columna calculada usando:

Calculo Costes Col =
VAR Lista =
    SUMMARIZE ( Facturas, Calendario[Fecha], 'Dim Producto'[Producto] )
VAR Fact1 =
    SELECTCOLUMNS (
        Facturas,
        "Fecha", Facturas[Fecha],
        "Producto", Facturas[Producto]
    )
RETURN
    CALCULATE ( [Calculo Costes], INTERSECT ( Fact1, Lista ) )

Y obtendrás...

De todas formas es importante tener en cuenta que hay que evitar cargar tablas con columnas calculadas. Es mucho mejor, desde el punto de vista de agilidad del modelo de datos, usar la primera opción con las medidas en lugar de usar la opción de la.columna calculada

Te adjunto el nuevo archivo

[jacob2102]

He probado la solución y sí consigo obtener los datos en una nueva tabla.

El problema me surge cuando incluyo una nueva variable en la tabla de Facturas y Costes, incluyendo la moneda en la que se realiza la transacción de la factura, y el coste de cada producto en base a la moneda en la que se venda.

Con esta nueva variable sólo consigo obtener el coste de cada producto cuando filtro en la fórmula por una moneda concreta, no consigo que me de el resultado dinámico según la moneda de la factura.

También he intentado generar una nueva alternativa, a través de una columna calculada en la tabla Facturas. (Sé que incrementa el consumo del archivo y que la mejor opción es la medida, pero a partir de ese valor tengo que generar cálculos de tipo de cambio y estoy probando opciones).

Obtengo el coste cuando la fecha factura es igual a la fecha de coste, pero cuando trato de incluir la fecha anterior más cercana tengo problemas, porque no soy capaz de concretar esa fecha más cercana.

Este es el código de la columna calculada que estoy utilizando, pero obtengo un #ERROR y no consigo el resultado que busco para las fechas anteriores:

Coste fecha anterior más cercana = 
   IF(ISBLANK(CALCULATE(VALUES(Costes[Coste]),
      FILTER(Costes,Costes[Producto]=Facturas[Producto] && 
             Costes[Moneda]=Facturas[Moneda] && Costes[Fecha]=Facturas[Fecha]))),
         CALCULATE(VALUES(Costes[Coste]),FILTER(Costes,Costes[Producto]=Facturas[Producto] && 
                   Costes[Moneda]=Facturas[Moneda] && Costes[Fecha]<Facturas[Fecha])), 
         CALCULATE(VALUES(Costes[Coste]),FILTER(Costes,Costes[Producto]=Facturas[Producto] && 
                   Costes[Moneda]=Facturas[Moneda] && Costes[Fecha]=Facturas[Fecha])))

Adjunto el nuevo archivo que incluye la variable de moneda, donde se detallan los cálculos que estoy aplicado para obtener como columna calculada. (no incluye los cáculos de las medidas, para diferenciar ambos ejemplos).

https://community.powerbi.com/t5/Desktop/Duplicar-filas-con-fechas-diferentes/m-p/2037653#M763738  

¿Conocéis alguna solución?

Gracias. Un saludo,

Jacob

[PaulDBrown]

Hola @jacob2102 

El enclace que has puesto no lleva al archivo nuevo. Lo puedes incluir?

Gracias

[jacob2102]

Hola @PaulDBrown 

Disculpa el error, me he confundido de enlace. Este es el enlace correcto:

https://drive.google.com/file/d/1heOdC5omBtv_qOCljaHI6-AaMCDDFcBy/view?usp=sharing  

Graicas. Un saludo,

Jacob

Gracias. Un saludo,

[PaulDBrown · Accepted solution]

A ver si esto te sirve...

!) Crea tablas de busqueda (Dimension Tables) para los campos Producto y Moneda usando:

Dim Producto = DISTINCT(Costes[Producto])

Dim Moneda = DISTINCT(Costes[Moneda])

2) crea relaciones 1:N entre estas tablas y sus campos correspondientes en las tablas de Facturas y Costes. El modelo debe quedar así:

3) crea  medidas para el cálculo de los costes por fecha:

Sum Coste = SUM(Costes[Coste])

Calculo Costes =
VAR PrevD =
    MAXX (
        FILTER (
            ALL ( Calendario ),
            Calendario[Fecha] <= MAX ( Calendario[Fecha] )
                && NOT ( ISBLANK ( [Sum Coste] ) )
        ),
        Calendario[Fecha]
    )
RETURN
    CALCULATE (
        [Sum Coste],
        FILTER ( ALL ( Calendario ), Calendario[Fecha] = PrevD )
    )

4) en la tabla factura, crea una nueva columna calculada con el código siguiente:

Coste por linea =
VAR Ref =
    SUMMARIZE (
        Facturas,
        Calendario[Fecha],
        'Dim Producto'[Producto],
        'Dim Moneda'[Moneda]
    )
VAR Fact1 =
    SUMMARIZE ( Facturas, Facturas[Fecha], Facturas[Producto], Facturas[Moneda] )
RETURN
    CALCULATE ( [Calculo Costes], INTERSECT ( fact1, ref ) )

Y la tabla factura quedará así:

[jacob2102]

Gracias @PaulDBrown ,

Esta solución es válida y encaja con mi modelo. Es necesario realizar diferentes modificaciones pero se consigue el resultado buscado, pudiendo enlazar todos los elementos.

Tengo que realizar más pruebas para confirmar su valía con el modelo total, pero hasta el momento es válida.

Gracias. Un saludo,

Jacob