Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IgorAM
Helper I
Helper I

Show the last research

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)

5 REPLIES 5
johnt75
Super User
Super User

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

Hi, @johnt75! Thank you for the answer!

 

Does not work :(!

IgorAM_0-1686059290183.png

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

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

Yes, it is: 

IgorAM_0-1686069961864.png

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


 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors