Previous NPS to enable calculation of change

[mark_carlisle]

I have the following example data;

Survey ID

Created Date

Customer Name

CSAT Score

CSAT

FCRValue

FCR Text

tNPS Score

tNPS Text

tNPSValue

tNPSSurvey

Account Number

Account Name

Account Type

Territory

FCR Target

CSAT Target

NPS Target

UKI00001

19 June 2019

Mark

10

100.0%

100.0%

One Time

8

Neutral

0

True

12345678

Comp 1

Customer

United Kingdom

90.0%

90.0%

60.0

UKI00002

13 June 2019

Mark

9

90.0%

100.0%

One Time

9

Promoter

10

True

12345678

Comp 1

Customer

United Kingdom

90.0%

90.0%

60.0

We can see that the customer has had two surveys and the first survey they were a NPS Promoter, on the latest survey they are an NPS Neutral. What I would like to do in DAX if possible is to have a calculated column that would show the previous tNPSText as shown below. Is this possible?

Survey ID

Created Date

Customer Name

CSAT Score

CSAT

FCRValue

FCR Text

tNPS Score

tNPS Text

tNPSValue

tNPSSurvey

Account Number

Account Name

Account Type

Territory

FCR Target

CSAT Target

NPS Target

PrevioustNPSText

UKI00001

19 June 2019

Mark

10

100.0%

100.0%

One Time

8

Neutral

0

True

12345678

Comp 1

Customer

United Kingdom

90.0%

90.0%

60.0

Promoter

UKI00002

13 June 2019

Mark

9

90.0%

100.0%

One Time

9

Promoter

10

True

12345678

Comp 1

Customer

United Kingdom

90.0%

90.0%

60.0

The actual data contains many customers and potentially many surveys for each customer, it is specifically the survey immediately prior to the latest survey I'm interested in.

Thanks

[amitchandak]

Hi @mark_carlisle,

Both tables are almost same. can you please hightlight what is need.

[mark_carlisle]

I've highlighted in red the new column I would like to create. The record in the first row for this new column contains the previous tNPSText for this customer, the record in the second row contains a null or blank beacuse this was the first survey for this customer.

[v-xuding-msft · Accepted solution]

Hi @mark_carlisle ,

Try the formula below. If you need to add other limiting conditions, you can reference "&& 'Table 1'[Created Date] < EARLIER('Table 1'[Created Date] )".

PrevioustNPSText = CALCULATE(MAX('Table 1'[tNPS Text]),FILTER('Table 1','Table 1'[Customer Name] = EARLIER('Table 1'[Customer Name]) && 'Table 1'[Created Date] < EARLIER('Table 1'[Created Date] )))

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Just realise there was a horizontal scroll too 

Please refer

https://community.powerbi.com/t5/Desktop/Obtain-the-last-value-in-a-column/td-p/306289

https://community.powerbi.com/t5/Desktop/Last-value-with-condition/td-p/455752

https://community.powerbi.com/t5/Desktop/Latest-Value/td-p/101257