Forum Discussion
Helper IIIMatrix for month over month data and the changes
I'm trying to create a this matrix from a table that contains the current month, previous month and the change in value and percentage.
My current data table looks like this...
| YearMonth | MonthName | Product | Total | CurrentMonth | PreviousMonth |
| 202112 | December | APPLES | 688 | NO | YES |
| 202201 | January | APPLES | 856 | YES | NO |
| 202112 | December | ORANGES | 5 | NO | YES |
| 202112 | December | PEARS | 75 | NO | YES |
| 202201 | January | PEARS | 88 | YES | NO |
| 202112 | December | KIWIFRUIT | 35 | NO | YES |
| 202201 | January | KIWIFRUIT | 40 | YES | NO |
| 202112 | December | BANANAS | 72 | NO | YES |
| 202201 | January | BANANAS | 27 | YES | NO |
| 202112 | December | STRAWBERRIES | 113 | NO | YES |
| 202201 | January | STRAWBERRIES | 87 | YES | NO |
| 202112 | December | BLUEBERRIES | 36 | NO | YES |
| 202201 | January | BLUEBERRIES | 38 | YES | NO |
| 202112 | December | WATERMELON | 7 | NO | YES |
| 202201 | January | WATERMELON | 7 | YES | NO |
| 202112 | December | PINEAPPLE | 13 | NO | YES |
| 202112 | December | PINEAPPLE | 10 | YES | NO |
| 202112 | December | CHERRIES | 1 | NO | YES |
| 202112 | December | ROCKMELON | 16 | NO | YES |
| 202112 | December | GRAPES | 2 | NO | YES |
This is how I would like the matrix to look...
| Product | This Month | Last Month | Change | Change % |
| APPLES | 856 | 688 | 168 | 24.4% |
| ORANGES | 0 | 5 | -5 | -100.0% |
| PEARS | 88 | 75 | 13 | 17.3% |
| KIWIFRUIT | 40 | 35 | 5 | 14.3% |
| BANANAS | 27 | 72 | -45 | -62.5% |
| STRAWBERRIES | 87 | 113 | -26 | -23.0% |
| BLUEBERRIES | 38 | 36 | 2 | 5.6% |
| WATERMELON | 7 | 7 | 0 | 0.0% |
| PINEAPPLE | 10 | 13 | -3 | -23.1% |
| CHERRIES | 0 | 1 | -1 | -100.0% |
| ROCKMELON | 0 | 16 | -16 | -100.0% |
| GRAPES | 0 | 2 | -2 | -100.0% |
I've tried so many ways to do this but I just can't make it work.
Can someone please help me with how to do this?
Many thanks
darls15
I would recommend you lose the "Current Month" and "Previous Month" columns - you don't want to box yourself in with that. Either use measures to figure out the max month, or use a slicer.
See attached for a sample implementation.
2 Replies
- lbendlin
Super User
I would recommend you lose the "Current Month" and "Previous Month" columns - you don't want to box yourself in with that. Either use measures to figure out the max month, or use a slicer.
See attached for a sample implementation.
onedayoverThank you lbendlin, I appreciate your advice re current/previous months. This is exactly what I needed and it's very much appreciated 🙂