Select data according to certain date

[Anonymous]

Hi everyone, 

I would ask for your support and knowledge to help me to solve this topic.

I have a set of prices, for few contracts, launched in some dates. I need to filter all the items which its Contract_Month and Contract_Year are above or equal to the latest date's month and year (30/10/2020).

As per example:

Date	Contract_Month	Contract_Year	Price
30/10/2020	01	2021	10
29/09/2020	09	2020	2,1
01/08/2020	01	2021	11
02/06/2020	08	2020	7
02/06/2020	12	2020	3

Desired output

Date	Contract_Month	Contract_Year	Price
30/10/2020	01	2021	10
01/08/2020	01	2021	11
02/06/2020	12	2020	3

Thanks in advance for any help you can give.

Regards,

[camargos88]

@Anonymous ,

No caso do filtro com 0, se comparar novembro de 2020 com março/abril de 2020 deveria retornar zero, não ?

No caso do filtro com 1, os anos são posteriores ao da data_cotacao, não ?

Talvez eu tenha entendido a necessidade errada, essa solução compara as datas em cada linha. Se for algo diferente disso, me explique novamente por favor.

[camargos88]

@Anonymous ,

Dá uma olhada no arquivo anexado.

Criei uma fórmula para comparar as datas e filtrar o visual:

_Filtro = 
VAR _dateCotacao = SELECTEDVALUE('Table'[DATA_COTACAO])
VAR _dateContrato = DATE(SELECTEDVALUE('Table'[Ano_Contrato]), SELECTEDVALUE('Table'[Mês Contrato]), 1)

RETURN IF(_dateContrato >= _dateCotacao, 1, 0)

[Anonymous]

Olá @camargos88 ! Vi o ficheiro que enviaste. Quando selecciono o filtro "0", obtenho 3 itens acima da ultima data do mercado. Da mesma forma se seleccionar o filtro "1", a query devolve 6 itens com mes/ano abaixo da ultima data do mercado.

[camargos88]

@Anonymous ,

You can create a measure like this:

_Filter = 
VAR _date = DATE(SELECTEDVALUE('Table'[Contract_Year]), SELECTEDVALUE('Table'[Contract_Month]),  1)
RETURN IF(_date >= DATE(2020, 10,30), 1, 0)

 

Use this measure to filter the visual.

[Anonymous]

Hi @camargos88 ! Thanks for your answer. I think that may succeed, but the date should be the MAX in the Date column, instead of static. I know well, that October 30th is the latestet in the example, but the data model will be updated on a daily basis.

Cheers

[camargos88]

@Anonymous ,

You can replace this block with:

CALCULATE(MAX(TABLE[DT_DATE]), ALL(TABLE))

[Anonymous]

Hi @camargos88 ! Sorry for the late feedback, but I'm just today coming back to this. I've tried:

ContratosVivos =

VAR _date = DATE(SELECTEDVALUE(UDM_DMO_FORWARD_VALOR[Ano_Contrato]), SELECTEDVALUE(UDM_DMO_FORWARD_VALOR[UDM_DMO_TIPO_CONTRATO.FIM]), 1)

RETURN IF(_date >= (CALCULATE(MAX(UDM_DMO_FORWARD_VALOR[DATA_COTACAO]), ALL(UDM_DMO_FORWARD_VALOR))),1,0).

The outcome is still returning items with all the months from 2020, and it should be all the forward months comparing to the latest date from the query. 

What do you suggest to correct this?

Thanks once more!

[camargos88]

@Anonymous ,

você pode colocar uma massa de dados aqui como exemplo ?

[Anonymous]

DATA_COTACAO	Ano_Contrato	VALOR	CONTRATO_TIPO	Mês Contrato
22-08-2012	2012	943	Ago	3
22-08-2012	2012	938	Set	3
22-08-2012	2012	949	Q4	3
22-08-2012	2013	972	Q1	3
22-08-2012	2013	988	Q2	3
22-08-2012	2013	10135	Q3	3
22-08-2012	2013	104	Q4	3
22-08-2012	2014	10855	Cal	3
22-08-2012	2015	11345	Cal	3
22-08-2012	2012	894	Ago	4
22-08-2012	2012	8895	Set	4
03-11-2020	2021	646	Q4	4
03-11-2020	2021	6427	Q3	4
03-11-2020	2021	6408	Q2	4
03-11-2020	2021	6343	Q1	4
03-11-2020	2021	6275	Jan	4
03-11-2020	2020	6215	Dez	4
03-11-2020	2020	6125	Nov	4
03-11-2020	2032	6572	Cal	3
03-11-2020	2031	6572	Cal	3
03-11-2020	2030	6572	Cal	3
03-11-2020	2029	6572	Cal	3
03-11-2020	2028	6572	Cal	3
03-11-2020	2027	6572	Cal	3
03-11-2020	2026	6572	Cal	3
03-11-2020	2025	6346	Cal	3
03-11-2020	2024	619	Cal	3
03-11-2020	2023	6015	Cal	3
03-11-2020	2022	5685	Cal	3
03-11-2020	2021	5451	Cal	3
03-11-2020	2021	5635	Q4	3
03-11-2020	2021	5515	Q3	3
03-11-2020	2021	5362	Q2	3
03-11-2020	2021	5292	Q1	3
03-11-2020	2021	5245	Jan	3
03-11-2020	2020	518	Dez	3

[Anonymous]

@camargos88 Na realidade só deverão aparecer os items que Mês_Contrato > mês da data de cotação, e ano_contrato => que ano da data cotação.