How to bring the price between dates?

[RaphaelQueiroz]

Hi, I have two tables, one has de prices of my company by iten, the another one has de price of others companies.

For example:

This is my company table

 

This is others companies's table

This is the result that I want (5th Collumm) 

The function that I'm using is:

Preço Empresa = CALCULATE(MEDIAN(TBhistorico_preco_venda[DFpreco]);FILTER(ALL(dCalendario);dCalendario[DATA]<=EARLIER(TBconcorrente_preco[Data])))

But the result are ignoring the date and bring the median of all serie.
So, how can I bring the DFpreço in my company table to my others companies table, taking into account the dates?

Thanks for the Help

[rafaelmpsantos · Accepted solution]

This one

Medida =
var datamaxima = calculate(LASTDATE(tabelaprecosempresa[DFdata_alteracao]);filter(all(tabelaprecosempresa[DFdata_alteracao]);tabelaprecosempresa[DFdata_alteracao]<=SELECTEDVALUE(tabelaprecosconcorrente[Data Coleta])))
return
CALCULATE(AVERAGE(tabelaprecosempresa[DFpreco]);filter(ALL(tabelaprecosempresa);tabelaprecosempresa[DFdata_alteracao]=datamaxima && tabelaprecosempresa[DFcod_item_estoque]=SELECTEDVALUE(tabelaprecosconcorrente[DFcod_item_estoque])))

[v-yulgu-msft]

Hi @RaphaelQueiroz,

Please try this:

Preço Empresa =
CALCULATE (
    MEDIAN ( TBhistorico_preco_venda[DFpreco] ),
    FILTER (
        ALL ( TBhistorico_preco_venda ),
        TBhistorico_preco_venda[Dfdata_alteracao]
            <= EARLIER ( TBconcorrente_preco[DataColeta] )
    )
)

Best regards,

Yuliana Gu

[RaphaelQueiroz]

Hi,

Does not work. He brings the average of all period, ignoring the date or the cod.

P.s.: When I put MEDIAN in function above, I would like to say AVERAGE.