Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Hi @mark_carlisle,
Both tables are almost same. can you please hightlight what is need.
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.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |