topic Re: Show the last research in DAX Commands and Tips

Show the last research

Anonymous — Tue, 06 Jun 2023 00:17:10 GMT

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)

Re: Show the last research

johnt75 — Tue, 06 Jun 2023 09:16:52 GMT

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

Re: Show the last research

Anonymous — Tue, 06 Jun 2023 13:49:25 GMT

Hi, johnt75! Thank you for the answer!

Does not work :(!

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

Re: Show the last research

johnt75 — Tue, 06 Jun 2023 13:57:12 GMT

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

Re: Show the last research

Anonymous — Tue, 06 Jun 2023 16:48:08 GMT

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

Re: Show the last research

johnt75 — Wed, 07 Jun 2023 08:29:32 GMT

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