Measure that looks for previous value by date, when grouping does not work

[Syndicate_Admin]

Hello Good morning everyone:

I have the following measurement:

Variacion (%) Precios (Manitou) = 
var _pre=CALCULATE(MAX('MANITOU'[fecha_creacion_lp]),FILTER(ALL('MANITOU'),'MANITOU'[Clave 1] =MIN('MANITOU'[Clave 1]) && 'MANITOU'[fecha_creacion_lp]

Lo que intento hacer es que en la tabla MANITOU busque el primer registro anterior a través del campo "clave 1", el cual se contruye con el codigo Fabricante LP-fecha. La tabla tiene la siguiente estructura y tiene la evolucion de precios para cada codigo de fabricante por cada fecha históricamente.

Al mostrar en una visualizacion tengo lo siguiente funcionando correctamente

Sin embargo, al dejar de filtrar por código y agrupar a nivel de categoria el resultado de la medida queda errónea

Lo que necesito es que al agrupar a nivel superior de código de fabricante realice la misma operacion inicial, para el caso de ejemplo sería buscar en la fecha anterior la suma de la agrupacion y calcular el crecimiento/disminucion del campo precio LP. La idea es que la medida funcione a cualquier nivel de agrupacion y no 1 en espeífico.

¿Como se puede hacer esto?

Muchas gracias por la ayuda.

[Syndicate_Admin]

Hello Good afternoon, will there be any suggestions to help me solve this problem?

I would appreciate it very much!

[Syndicate_Admin]

I thank you for the answer, but I still could not apply it, I think it was my fault, by not showing how the visualization and the data model is built.

The data model is as follows (the unions that are not seen are the same as the Manitou table, but for other brands)
calendar by date--->MANITOU by LP Creation Date
CPI SKC by Key1---> MANITOU by Key1 where Key1 is a calculated column

Clave 1 = 'IPC SCK'[Código Fabricante IPC SKC]&"-"&'IPC SCK'[Marca Final]

Clave 1 = MANITOU[Código Fabricante LP]&"-"& MANITOU[marca]

The visualization is a matrix, has fields of the 3 tables plus the measures already built

The rows and columns of the matrix are as follows

Date --> calendar table

marca final --> tabla IPC SKC

linea --> tabla IPC SKC

category --> KPI table SKC

manufacturer code IPC SKC -->table IPC SKC

Price LP Manitou (measured)-->

Precio LP (Manitou) = CALCULATE(SUM('MANITOU'[Precio LP]),LASTNONBLANK('MANITOU'[fecha_creacion_lp],SUM('MANITOU'[Precio LP])))

Price change (%) (Manitou) --> measure to change set out above

Variation ($) Prices (Manitou) --> measure to change but in $

Seeing the presupposed formula, all the SALECTVALUE I changed them by the SKC IPC table, since there the data is found, except for the first one that corresponds to the date of creation of the MANITOU table.

The problem I have in the ALLSELECTED, since I refer to the MANITOU table for the date, but the values are from the SKC IPC table. Specifically that section of the measure is the one that I do not know how to correct

VAR _predate =
    CALCULATE (
        MAX ( MANITOU[Fecha Creación LP] ),
        FILTER (
            ALLSELECTED ('IPC SCK'),
            'MANITOU'[Fecha Creación LP] < _seldate
                && 'MANITOU'[marca] = _selMFinal
                && 'MANITOU' = _selLinea
                && 'MANITOU'[Categoria] = _selcategoria
                && 'MANITOU'[Codigo Fabricante IPC SKC] = _selCFSKC
        )
    )

This for both variables (_predate and _pre)

My idea was to do this

CALCULATE (
        SUM ( 'MANITOU'[fecha_creacion_lp] ),
        FILTER (
            ALLSELECTED ( 'IPC SCK' ),
            'MANITOU'[Fecha Creación LP] <= _predate
                && 'IPC SCK'[Marca Final] = _selMFinal
                && 'IPC SCK'[Linea] = _selLinea
                && 'IPC SCK'[Categoría] = _selcategoria
                && 'IPC SCK'[Código Fabricante IPC SKC] = _selCFSKC
        )
    )

The error apparently is in the referencing of the MANITOU table for the date, but in the SKC IPC table I have no date.

I thank you very much for your help in making the correction.

Best regards.

PS: sorry not to share the .pbix, but it is on a remote desktop from which I can get absolutely nothing because of blocks and firewalls applied.

[Anonymous]

Hi @Petazo_ ,

Please update the formula of measure [] as below and check if it can return the correct result...

Variacion (%) Precios (Manitou) =
VAR _seldate =
    SELECTEDVALUE ( 'MANITOU'[Date] )
VAR _selMFinal =
    SELECTEDVALUE ( 'MANITOU'[Marca Final] )
VAR _selLinea =
    SELECTEDVALUE ( 'MANITOU'[Linea] )
VAR _selcategoria =
    SELECTEDVALUE ( 'MANITOU'[Categoria] )
VAR _selCFSKC =
    SELECTEDVALUE ( 'MANITOU'[Codigo Fabricante IPC SKC] )
VAR _cur =
    SUM ( 'MANITOU'[fecha_creacion_lp] )
VAR _predate =
    CALCULATE (
        MAX ( 'MANITOU'[Date] ),
        FILTER (
            ALLSELECTED ( 'MANITOU' ),
            'MANITOU'[Date] < _seldate
                && 'MANITOU'[Marca Final] = _selMFinal
                && 'MANITOU'[Linea] = _selLinea
                && 'MANITOU'[Categoria] = _selcategoria
                && 'MANITOU'[Codigo Fabricante IPC SKC] = _selCFSKC
        )
    )
VAR _pre =
    CALCULATE (
        SUM ( 'MANITOU'[fecha_creacion_lp] ),
        FILTER (
            ALLSELECTED ( 'MANITOU' ),
            'MANITOU'[Date] = _predate
                && 'MANITOU'[Marca Final] = _selMFinal
                && 'MANITOU'[Linea] = _selLinea
                && 'MANITOU'[Categoria] = _selcategoria
                && 'MANITOU'[Codigo Fabricante IPC SKC] = _selCFSKC
        )
    )
RETURN
    DIVIDE ( _cur - _pre, _pre )

If the above one can't help you figure out, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards