topic Re: Obtener costo promedio in DAX Commands and Tips

Obtener costo promedio

Anonymous — Mon, 26 Aug 2024 23:18:33 GMT

Hola a todos

Tengo el siguiente problema.

Quiero obtener el costo promedio de los siguientes movimientos, logre obtener el valor de la fecha de una fila anterior esto ya que el costo promedio debe de ser el mismo hasta que cambien el movimiento a una entrada de importacion y re hacer el calculo de "costo X Tipo de Cambio" X "Saldo final", de la siguiente manera recuró la fecha de la fila anterior

_Costo Unitario Calculado =

VAR fechamin = MAX(Movimientos[UltimoCambio])

VAR fecha_anterior = CALCULATE(MAX(Movimientos[UltimoCambio]),TOPN(1,FILTER(ALLSELECTED(Movimientos),Movimientos[UltimoCambio]< fechamin)))

RETURN fecha_anterior

y asi es como deberia de quedar

Re: Obtener costo promedio

Anonymous — Tue, 27 Aug 2024 06:28:04 GMT

Hi, Anonymous

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Insert the index column：

3. Below are the measure I've created for your needs:

MEASURE = IF ( MAX ( 'Movimientos'[Mov] ) <> "2.1 Factura", CALCULATE ( SUM ( 'Movimientos'[Costo x Tipo de Cambio] ), FILTER ( ALLSELECTED ( 'Movimientos' ), 'Movimientos'[Index] = MAX ( 'Movimientos'[Index] ) + 1 ) ), SUM ( 'Movimientos'[Costo x Tipo de Cambio] ) )

4.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Obtener costo promedio

Anonymous — Tue, 27 Aug 2024 15:54:38 GMT

Hi @v-linyulu-msft

Thank you for your help

My index for me is the column "UltimoCambio" and I have this excel that can help you to understand better

this is how it should look and you can look that when it switch mov for "entrada importacion" the calculated cost changes

Kind Regards

Re: Obtener costo promedio

Anonymous — Wed, 28 Aug 2024 03:04:13 GMT

Hi, Anonymous

Thank you for your correction, but I think I did not express clearly the reason why I chose to insert index.

If I use the "UltimoCambio" column you provided as the index column, it is difficult to uniquely identify the number of rows because it has the same content, so I choose to insert an index column as the identifier. And my initial judgment statement also takes into account the "UltimoCambio" column.

Thank you for your understanding.

Of course, if you have any new ideas, you are welcome to contact us.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.