Forum Discussion
Latest Score - Previous Score
Hello,
I'm new with DAX but I have managed to create calculated columns, calculated fields. Now, with this simple table I'm trying to figure out how to calculate the difference between the latest and the second latest scores, skipping the months with no data.
Ex. for Dothan, AL should be 100.00% - 96.71%
| State | Site | 2017.08 | 2017.09 | 2017.10 | 2017.11 | 2017.12 | 2018.01 | 2018.02 | 2018.03 | 2018.04 | 2018.05 | 2018.06 | 2018.07 |
| AL | Dothan, AL | 100.00% | 100.00% | 96.71% | 100.00% | ||||||||
| Florence, AL | 100.00% | 100.00% | 99.18% | 100.00% | 100.00% | 100.00% | 100.00% | 100.00% | |||||
| Gadsden, AL | 100.00% | 100.00% | 100.00% | 100.00% | 100.00% | ||||||||
| Huntsville, AL | 100.00% | 100.00% | 100.00% | 100.00% | |||||||||
| Mobile, AL | 99.18% | 97.81% | 92.05% | 96.16% | 99.18% | 95.89% | |||||||
| Tuscaloosa, AL | 99.18% | 100.00% | 100.00% | 98.90% | 100.00% | ||||||||
| AR | Little Rock, AR | 96.44% | 98.36% | 95.89% | |||||||||
| FL | Boca Raton, FL | 98.77% | 98.17% | 99.45% | 99.45% | 100.00% | 97.53% | 85.75% | |||||
| Bonita Springs, FL | 98.90% | 98.90% | 99.45% | 96.71% | |||||||||
| Davie, FL | 100.00% | 100.00% | 97.53% | 95.62% | 92.05% |
Any help is greatly appreciated.
Thanks.
2 Replies
Hi VictorC,
"On the below Pivot Table I need to add another column that calculates the difference between the latest and the second latest scores."
<---- Could you clarify more details about the logic of how to achieve the difference between the lastest and the second lastest scores in one row. In addtion, would you use some calculate columns or measures to show the logic?
Regards,
Jimmy Tao
Hi Jimmy,
The %s you see in the pivot table are an average of multiple scores within the specific month and city . What I need is another column that shows me the difference between the last 2 months that have a value (skipping the months with blank scores)
