último precio de compra sin considerar fecha mínima de filtro

[lmiguez]

Buenas tardes, necesito ayuda con una medida y visualización. Tengo una tabla de movimientos de stock por artículo con las compras y precio de compra. Por otro lado tengo una tabla de fechas (calendario).

En un informe tengo el filtro de fechas de la tabla calendario. Y en una visuación de matriz necesito tener por artículo el precio de la última compra realizada (al utilizar la fórmula me considera la fecha inicial del período y necesito que no la considere). Quiero ver el stock en un período, y necesito ese stock valorizado, para lo cual necesito el precio de la última compra. El problema es que la compra de ese stock pudo haberse realizado antes de la fecha del período que estoy seleccionando. 

Probe con la siguiente fórmula pero me sigue limitando la búsqueda del precio entre el periodo que le estoy seleccionando.

La fórmula que estoy usando es la siguiente:

Prueba Ultimo precio de compra = CALCULATE(MAX('Movimientos Stock'[Precio unitario en USD equiv]),ALL(Calendario),FILTER('Movimientos Stock','Movimientos Stock'[Tipo de movimiento]="1-Compra"),FILTER('Movimientos Stock','Movimientos Stock'[Fecha]>=DATE(2020,03,01)&& 'Movimientos Stock'[Fecha]<=MAX('Movimientos Stock'[Fecha])))

He probado con ponerle la función all y la función removefilters pero ninguna me borra la fecha mínima del período seleccionado.

Muchas gracias!

Saludos,

Lucía

[v-tsaipranay]

Hi @lmiguez ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[lmiguez]

Hi v-tsaipranay! 

The issue continues.

Best regards,

Lucía

[v-tsaipranay]

Hi  @lmiguez, 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thnak you.

[v-tsaipranay]

Hi @lmiguez ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[lmiguez]

Hi v-tsaipranay!

No, the issue continues. I will try modifying the model.

Thank you!

Best regards,

Lucía

[v-tsaipranay]

Hi @lmiguez ,

Thank you for the update, we really appreciate the effort you're putting in.

Since the issue is still occurring, adjusting the data model might give you more flexibility. This can help separate the calculation of the last purchase price from the date slicer, which seems to be affecting it right now.

If you decide to give the TREATAS() approach another try, it’s still a valid option for keeping the item context while ignoring the date filters. You can also refer to the official documentation here for more details: TREATAS function (DAX) – Microsoft Learn

Feel free to reach out if you need help while updating the model or trying other solutions  we’re happy to assist further.

If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.

[lmiguez]

Thank you very much @Akash_Varuna and @v-tsaipranay . The issue persists, and I understand it's due to the relationship between tables — the calendar table, which I use in the slicer, and the stock movements table. I can't make the relationship bidirectional because doing so breaks other formulas that use DATEADD.

kind regards,

Lucía

[v-tsaipranay]

Hi @lmiguez ,

You're correct, this behavior is caused by the single-directional relationship between your calendar and stock movement tables. While making the relationship bidirectional could resolve the immediate issue, it would indeed interfere with DAX time intelligence functions like , which require a single-direction relationship from the calendar. DATEADD

To work around this and still retrieve the correct last purchase price regardless of the selected date period, you can explicitly inject the item context into the Stock Movements table using . This avoids the need to modify your data model and ensures slicers or visuals filter as expected. TREATAS

Here's a revised measure that should work in your setup:

Last Purchase Price per Item =
VAR LastPurchaseDate =
    CALCULATE(
        MAX('Stock Movements'[Date]),
        FILTER(
            ALL('Stock Movements'),
            'Stock Movements'[Movement Type] = "1-Buy"
        ),
        TREATAS(VALUES('Item'[Item]), 'Stock Movements'[Item])
    )
RETURN
    CALCULATE(
        MAX('Stock Movements'[Unit Price in USD equiv]),
        FILTER(
            ALL('Stock Movements'),
            'Stock Movements'[Movement Type] = "1-Buy" &&
            'Stock Movements'[Date] = LastPurchaseDate
        ),
        TREATAS(VALUES('Item'[Item]), 'Stock Movements'[Item])
    )

This measure ensures that the last purchase price is evaluated independent of the date filter, while preserving the item filter context from your matrix visual.

Hope this helps. Please reach out for further assistance.

If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.

[v-tsaipranay]

Hi @lmiguez ,

Thank you for reaching out to Microsoft fabric community forum. Also thank you @Akash_Varuna  for your inputs.

The issue arises because the slicer filters are still impacting your current measure. To resolve this, you can use the following DAX measure, which ignores the date filter when evaluating the last purchase price:

Last Purchase Price per Item =
VAR SelectedItem = SELECTEDVALUE('Stock Movements'[Item])
VAR LastPurchaseDate =
    CALCULATE(
        MAX('Stock Movements'[Date]),
        FILTER(
            ALL('Stock Movements'),
            'Stock Movements'[Movement Type] = "1-Buy" &&
            'Stock Movements'[Item] = SelectedItem
        )
    )
RETURN
    CALCULATE(
        MAX('Stock Movements'[Unit Price in USD equiv]),
        FILTER(
            ALL('Stock Movements'),
            'Stock Movements'[Movement Type] = "1-Buy" &&
            'Stock Movements'[Item] = SelectedItem &&
            'Stock Movements'[Date] = LastPurchaseDate
        )
    )

This ensures the latest available purchase price is always shown, independent of the selected period, while your stock data continues to respect the slicer.

Hope this helps. Please reach out for further assistance.

If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.

[Akash_Varuna]

Hi @lmiguez I think the issue is because the calendar filter isn't fully removed. Could you try this formula and check:

Last Purchase Price = 
CALCULATE(
    MAX('Stock Movements'[Unit Price in USD equiv]),
    REMOVEFILTERS('Calendar'),
    'Stock Movements'[Movement Type] = "1-Purchase",
    FILTER('Stock Movements', 'Stock Movements'[Date] <= MAX('Stock Movements'[Date]))
)

This ignores the calendar’s minimum date filter but keeps other filters like item and movement type intact. Ensure relationships and column formats are correct.