Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
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 :(!
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:
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.
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |