Medida para trazer último dia do mês anterior à data máxima do contexto

[pedrohmfortunato]

Pessoal,

Estou tentando fazer uma página onde consigo calcular o retorno acumulado de um fundo a partir das datas no filtro de segmentação.

Consegui executar com sucesso as medidas para trazer a  data de inicio do fundo, a mínima (da segmentação) e a máxima (da segmentação).

Porém, de início eu gostaria de retornar a última data do mês anterior para que eu calculasse o retorno mensal, que é calculado pela "divisão da cota atual pela cota do último dia do mês anterior - 1 -->   var = (cota data atual / cota data ultimo dia mes ant) - 1

Acontece que estou travado no retorno do ultimo dia do mês anterior à data máxima do contexto, e na sequência eu gostaria de retornar as datas de 3m, 6m e 12 meses anteriores (também) à data máxima do contexto. Podem me ajudar?

 

Seguem abaixo as medidas que mencionei e em anexo a base do fQuantum e o .pbix

Link para o PBIX: https://azquestinvestimento-my.sharepoint.com/:u:/g/personal/pedro_fortunato_azquest_com_br/EU_66VuwKWpCqrQOy4K2k50B6DjFZtMy_h9ZTzhVtMWo9Q?e=XS4JmV

fQuantum.xlsx

 

Data de Inicio do Fundo:

Data_ITD_QT = 

VAR
    vDATAITD = 
    
    CALCULATE(
    MIN(fQuantum[Data]);REMOVEFILTERS(fQuantum[Data]))

RETURN
        IF(
            SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])=BLANK();"N/A";
            IF(
                SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])="TODOS";"N/A";
                vDATAITD
            ))
            

Data Minima:

Data_Min_QT = 

var 
    vDataMin = MIN(fQuantum[Data])

RETURN
    IF(
            SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])=BLANK();"N/A";
            IF(
                SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])="TODOS";"N/A";
                vDataMin
            ))
 

Data Máxima:

Data_Max_QT = 

var 
    vdataMax = MAXX(fQuantum;fQuantum[Data])
RETURN
   IF(
            SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])=BLANK();"N/A";
            IF(
                SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])="TODOS";"N/A";
                vdataMax
            ))
    

Editado Novembro 29, 2021 por pedrohmfortunato

[Michele]

Olá @pedrohmfortunato!

Acredito que as medidas abaixo retornem o que você precisa, dá uma olhada, por favor.

Último Dia Mês Anterior =
VAR vUltimoDiaFiltro = MAXX(fQuantum,fQuantum[Data])
VAR vDataFinalMesAnterior = EOMONTH(vUltimoDiaFiltro,-1)

RETURN
    vDataFinalMesAnterior

 

3 meses anteriores =
VAR vLastDate = LASTDATE(fQuantum[Data])
VAR v3Meses =  DATEADD(vLastDate, -3, MONTH)
VAR v6Meses =  DATEADD(vLastDate, -6, MONTH)
VAR v12Meses =  DATEADD(vLastDate, -12, MONTH)

RETURN
    v3Meses

[pedrohmfortunato]

Michele,

 

Muito obrigado pela pronta resposta.

 

No entanto, eu preciso pegar a última data do calendario que tenha dados. Isto é, onde dCalendarioQuantum[Possui Dados?]="true".

 

No exemplo, seria 29/10/2021. Alguma ideia?

[Roseane]

 

@pedrohmfortunato bom dia,

Fiz um teste aqui com a medida abaixo para trazer a última data com dados, veja se lhe atende.

 

[pedrohmfortunato]

Michele,

 

Muito obrigado pela pronta resposta.

 

No entanto, eu preciso pegar a última data do calendario que tenha dados. Isto é, onde dCalendarioQuantum[Possui Dados?]="true".

 

No exemplo, seria 29/10/2021. Alguma ideia?

[pedrohmfortunato]

@Roseane @Michele

 

Super obrigado pelas dicas!!! Me ajudaram demais!

Por fim, consegui todas as datas. Seguem as medidas!

Data de Fechamento do Mês Anterior à data escolhida na segmentação de dados:

Data_MTD_QT = 

VAR vUltimaData = 

CALCULATE(
    Month(MAX(dCalendarioQT[Data]));
    dCalendarioQT[Possui Dados?] = "TRUE"
)

VAR VFimMesAnterior = 
    CALCULATE(
        MAX(dCalendarioQT[Data]);
        dCalendarioQT[Mês] < vUltimaData && dCalendarioQT[Possui Dados?] = "TRUE")

return
IF(
            SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])=BLANK();"N/A";
            IF(
                SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])="TODOS";"N/A";
format(VFimMesAnterior;"dd/mm/yyyy")
            ))
 

DATA DA PRIMEIRA COTA DO FUNDO ESCOLHIDO NA SEGMENTAÇÃO DE DADOS:

Data_ITD_QT = 

VAR
    vDATAITD = 
    
    CALCULATE(
    MIN(fQuantum[Data]);
    FILTER(
        ALL(dCalendarioQT);
        dCalendarioQT[Possui Dados?]="TRUE"
    ))
        

RETURN
IF(
            SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])=BLANK();"N/A";
            IF(
                SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])="TODOS";"N/A";
                format(vDATAITD;"dd/mm/yyyy")
            ))
            

DATA MÁXIMA ESCOLHIDA NA SEGMENTAÇÃO DE DADOS:

Data_Max_QT = 

var 
    vdataMax = MAXX(fQuantum;fQuantum[Data])
RETURN
   IF(
            SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])=BLANK();"N/A";
            IF(
                SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])="TODOS";"N/A";
                format(vdataMax;"dd/mm/yyyy")
            ))
    
DATA MÍNIMA ESCOLHIDA NA SEGMENTAÇÃO DE DADOS:

Data_Min_QT = 

var 
    vDataMin = MIN(fQuantum[Data])

RETURN
    IF(
            SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])=BLANK();"N/A";
            IF(
                SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])="TODOS";"N/A";
                format(vDataMin;"dd/mm/yyyy")
            ))


DATA 3 MESES ANTERIOR À DATA MAXIMA ESCOLHIDA NA SEGMENTAÇÃO:

 

Data_3M_QT = 

var  vLastDate = LASTDATE(fQuantum[Data])
VAR v3Meses =  DATEADD(vLastDate;-3;MONTH)

return
IF(
            SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])=BLANK();"N/A";
            IF(
                SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])="TODOS";"N/A";
            v3Meses

            ))

E, por fim, graças a estas datas, pude fazer o cálculo de retorno do fundo:

Retorno_ITD = 

var vDataITD = format(Medidas[Data_ITD_QT];"dd/mm/yyyy")
var vDatamax = format(Medidas[Data_Max_QT];"dd/mm/yyyy")

var vFundoSelec = SELECTEDVALUE(dFundosSQL_FiltroStatus[Fundo_Abrev])
var vCodAnbima = LOOKUPVALUE(dFundosSQL_FiltroStatus[Cod_Anbima];dFundosSQL_FiltroStatus[Fundo_Abrev];vFundoSelec)
var vConcatenaITD = vcodAnbima&vDataITD
var vConcatenaMax = vcodAnbima&vDatamax
var vCotaITD = LOOKUPVALUE(fQuantum[Cota];fQuantum[Concatenate];vConcatenaITD)
var vCotaMAX = LOOKUPVALUE(fQuantum[Cota];fQuantum[Concatenate];vConcatenaMax)

var Retorno_ITD = DIVIDE(vCotaMAX;vCotaITD;"N/A") - 1

RETURN

FORMAT(Retorno_ITD;"0.00%")

Segue o print do Visual.

*Neste caso, a primeira cota do fundo selecionado é 01/06/2005, a data minima escolhida na segmentação de dados é ignorada.

 

        Obrigado!!!!!!!!