Find the last day value;

[fleal]

Please, I need some help.

I intend to calculete the day by day price variation between the values of public traded stocks using DAX.After hour of reserch, I constructed the following expression, that gave me the current and the last price:

SUMMARIZE (
    ADDCOLUMNS (
        Inf_diario,
        "PREVIOUS VL",
        VAR previousDate =
            MAXX (
                FILTER (
                    Inf_diario,
                    Inf_diario[CNPJ_FUNDO] = EARLIER ( Inf_diario[CNPJ_FUNDO] )
                        && Inf_diario[DT_COMPTC] < EARLIER ( Inf_diario[DT_COMPTC] )
                ),
                Inf_diario[DT_COMPTC]
            )
        RETURN
            MAXX (
                FILTER (
                    Inf_diario,
                    Inf_diario[CNPJ_FUNDO] = EARLIER ( Inf_diario[CNPJ_FUNDO] )
                        && Inf_diario[DT_COMPTC] = previousDate
                ),
                Inf_diario[VL_QUOTA]
            )
    ),
    Inf_diario[ID],
    Inf_diario[DT_COMPTC],
    Inf_diario[VL_QUOTA],
    [PREVIOUS VL]
)

The results:

 

Now, I need to find the diference between the PREVIOUS VL and VL QUOTA (current value), to finally calculate Standard deviation, %change etc.

Tks in advance

[mattbrice · Accepted solution]

It seems you are modeling off a single table and created a second calculated table. If so, i would do things a bit differently.  I would use measures instead.   First, put 'id' and 'DT_COMPTC' on rows of Matrix, then add the following measures:

VL_Quota measure = SUM (Inf_diario[VL_Quota] )

then:

Previous VL Measure =
VAR _dates =
    FILTER (
        ALL ( Inf_diario[DT_COMPTC] ),
        Inf_diario[DT_COMPTC] < MAX ( Inf_diario[DT_COMPTC] )
    )
VAR _previousDate =
    LASTNONBLANK ( _dates, [VL_Quota measure] )
RETURN
    CALCULATE ( [VL_Quota measure], Inf_diario[DT_CoMPTC] = _previousDate )

then your % change is easy:

% Change =
DIVIDE ( [Previous DL Measure] - [VL_Quota measure], [VL_Quota measure] )

and so on.

But if you want to continue to use your calculated table, you'll need to add more values via the ADDCOLUMNS you have.

[fleal]

It worked perfectly!!! Thank you mattbrice.