Show the last research

[IgorAM]

Hi guys, need your help!

I have a sheet with some NPS researchs and the date for each research. I uploaded this at power bi and made a relation with calendar table.

Now what I need is show the last NPS research when I filter month and year. 

For example:

I have 3 researches in this dates

1 NPS: 11/04/2022

2 NPS: 20/11/2022

3 NPS: 15/05/2023

- if I select year 2022 and month 4 I want the result for the first NPS;

- if I select year 2022 and month 5 I still want the result for the first NPS, because it's the last until this date;

- if I select year 2022 and month 12 I want the result for the seconde NPS;

So, what I need it's always the very last research until the date that I filtered.

Here is example base, we have promoters, detractor and passives. Link: https://drive.google.com/drive/folders/1A8sRH_YUFI6OpIK96d9vNeQUyFnO10cn?usp=sharing

NPS is defined by:

NPS = (sum promoters)/(total responses) - (sum detractors)/(total responses)

[johnt75]

Try

NPS =
VAR CurrentDate =
    MAX ( 'Date'[Date] )
VAR PrevDate =
    CALCULATE ( MAX ( Responses[Date] ), 'Date'[Date] <= CurrentDate )
VAR TotalResponses =
    CALCULATE ( COUNTROWS ( Responses ), 'Date'[Date] = PrevDate )
VAR Promoters =
    CALCULATE (
        SUM ( Responses[Score] ),
        TREATAS (
            { ( PrevDate, "Promoter" ) },
            'Date'[Date],
            Responses[classification]
        )
    )
VAR Detractors =
    CALCULATE (
        SUM ( Responses[Score] ),
        TREATAS (
            { ( PrevDate, "Detractor" ) },
            'Date'[Date],
            Responses[classification]
        )
    )
VAR Result =
    DIVIDE ( Promoters, TotalResponses ) - DIVIDE ( Detractors, TotalResponses )
RETURN
    Result

[IgorAM]

Hi, @johnt75! Thank you for the answer!

Does not work :(!

The result is "Blank". I think we should consider the filters in currentDate variable!

[johnt75]

Is your date table marked as a date table? Is it linked to the date in the responses table ?

[IgorAM]

Yes, it is: 

and that is my measure

    VAR data_atual = MAX('dim_calendário'[Data])
    VAR data_nps = CALCULATE(MAX(fato_nps[Data]), 'dim_calendário'[Data] <= data_atual)
    VAR total_respostas = CALCULATE(COUNTROWS(fato_nps), 'dim_calendário'[Data] = data_nps)
    VAR Promotores = 
                    CALCULATE(SUM(fato_nps[Nota]), 
                                TREATAS({(data_nps, "Promotores")}, 
                                        'dim_calendário'[Data],
                                        fato_nps[Classificação]
                                        )
                            )
    VAR Detratores = CALCULATE(SUM(fato_nps[Nota]), 
                                TREATAS({(data_nps, "Promotores")}, 
                                        'dim_calendário'[Data],
                                        fato_nps[Classificação]
                                        )
                            )
    VAR Resultado = DIVIDE(Promotores, total_respostas) - DIVIDE(Detratores, total_respostas)
RETURN
    Resultado

[johnt75]

To see where the problem lies I would edit the measure to return the different variables which are declared.