help getting the value of a calculated column

[carlosmorales]

Hello everyone, thank you very much for your time and attention.

I need your help with the following, to be able to obtain the amount of the previous week from my data.

I got the amount of the current week, like this:

and I thought that with the following calculated column, I would get the value of the previous week:

in order to calculate the variation between the current week and the previous week.

data

Thanks for the help.

[parry2k · Accepted solution]

@carlosmorales solution attached, hope that is what you are looking for

[MFelix · Accepted solution]

Hi @carlosmorales ,

Try the following code:

SemAnt =
CALCULATE (
    MAX ( 'CXC Detalle'[semactual] );
    FILTER (
        'CXC Detalle';
        'CXC Detalle'[semana]
            = EARLIER ( 'CXC Detalle'[semana] ) - 1
    );
    'CXC Detalle'[Cliente] = EARLIER ( 'CXC Detalle'[Cliente] );
    'CXC Detalle'[Rangos Vencimiento]
        = EARLIER ( 'CXC Detalle'[Rangos Vencimiento] )
)

PBIX file attach.

Although I must advise that looking at the data you have you should make measures and not calculated columns since this will add size to your model. Using calculated measure that will alow more flexebility in the use of the data:

Monto = SUM('CXC Detalle'[Monto USD])

Previous week Monto =
CALCULATE (
    SUM ( 'CXC Detalle'[Monto USD] );
    FILTER (
        ALL ( 'CXC Detalle'[semana] );
        'CXC Detalle'[semana]
            = MAX ( 'CXC Detalle'[semana] ) - 1
    )
)

Variation = [Monto] - [Previous week Monto]

Be aware that the previous week need to be adjust in case you have more than 1 year on your data but it's similar to what is written.

As you can see  on the file using measure the result is the same.

Regards,

MFelix

[carlosmorales]

Hi,

@parry2k  and @MFelix 

good morning!

please excuse me for not responding before.

Thank you very much for your help, the two solutions are excellent.

Thank you for your time and knowledge to solve my dilemma.

[parry2k · Accepted solution]

@carlosmorales solution attached, hope that is what you are looking for