How can I calculate the average price from the previous day, filtering by groups?

[brucruz]

I have been trying to perfom a dax calculation without success. My objective is to obtain the average of the column [Média de Preco] considering some filters, which I was able to do with the code bellow.

MediaPB_Band_AI_Dia = 
CALCULATE(
    dPBomba_AI_FL[.PBomba_Medio];
    ALLEXCEPT(dPBomba_AI_FL;
        dPBomba_AI_FL[Data Coleta];
        dPBomba_AI_FL[Área de Influência];
        dPBomba_AI_FL[Combustivel];
        dPBomba_AI_FL[Bandeira]
    )
)

The problem is that I need to also calculate for each row the same expression considering the previous day. That means: to obtain the average price of a set of filters, but filtering not the day expressed in the same row, but the equivalent of the previous day.

I have already tried some codes, without sucess, like:

∆PBMedio%_Dia = 
CALCULATE(
    AVERAGE(dPBomba_AI_FL[Média de Preco]);
    dPBomba_AI_FL[Data Coleta]-1;
    ALLEXCEPT(
        dPBomba_AI_FL;
        dPBomba_AI_FL[Área de Influência];
        dPBomba_AI_FL[Combustivel];
        dPBomba_AI_FL[Bandeira]
    )
)

Could you please help me accomplish this?

Thank you,

Bruno

[v-juanli-msft]

Hi @brucruz 

Is my understanding correct?

If not, use my example to illustrate your expected result.

Área de Influência	Combustivel	Bandeira	.PBomba_Medio	Data Coleta	sum	average	rule
a	b	c	1	10/2/2018	1+2	(3/1)	(=sum/days)
a	b	c	2	10/2/2018	1+2	(3/1)
a	b	c	3	10/3/2018	1+2+3	(6/2)
a	b	c	4	10/4/2018	3+4	(7/2)
a	b	c	5	10/5/2018	4+5	(9/2)

Best Regards
Maggie