Forum Discussion

RaphaelQueiroz's avatar
RaphaelQueiroz
Frequent Visitor
7 years ago
Solved

How to bring the price between dates?

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

Need Help
  • rafaelmpsantos's avatar
    rafaelmpsantos
    7 years ago

    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])))

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      RaphaelQueiroz
      Frequent Visitor
      7 years ago

      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.

  • rafaelmpsantos's avatar
    rafaelmpsantos
    Icon for Responsive Resident rankResponsive Resident
    7 years ago

    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])))