Forum Discussion
Calculate the difference between quantities per month over two years
I am trying to do a simple calculation that is not being very easy to do. I am trying to find the difference in quantity for each month in 2019 and 2020. I split out the creation date into Month and Year so I can total them and then find the difference. My visualization has three columns and I am trying to calculate the forth (see below). ALL the data is in one Table. My row is Month, column is Year and value I am summing is Drop Quantity. I tried different commands but I guess I just don't know DAX good enough yet.
| Month | 2019 | 2020 | Diff |
| 1 | 555 | 444 | |
| 2 | 666 | 222 | |
| 3 | 777 | 555 | |
| 4 | 888 | 444 | |
| 5 | 999 | 333 | |
| 6 | 111 | 50 | |
| 7 | 222 | ||
| 8 | 333 | ||
| 9 | 444 | ||
| 10 | 555 | ||
| 11 | 666 | ||
| 12 | 777 |
Not proud of this one but here goes:
Create two measures
2019 =var m = SELECTEDVALUE('Values'[Month])return calculate(sum('Values'[Value]),'Values'[Year]=2019,'Values'[Month]=m)2020 =var m = SELECTEDVALUE('Values'[Month])return calculate(sum('Values'[Value]),'Values'[Year]=2020,'Values'[Month]=m)Then create a matrix visual with the Month as rows. NOTHING as columns, and the two measures as values.Lastly create another measurediff = [2020]-[2019]and also add to the values area.May need cleanup for the scenario where 2020 values are blank - not sure what you want to do in such a case.
2 Replies
- lbendlin
Super User
Not proud of this one but here goes:
Create two measures
2019 =var m = SELECTEDVALUE('Values'[Month])return calculate(sum('Values'[Value]),'Values'[Year]=2019,'Values'[Month]=m)2020 =var m = SELECTEDVALUE('Values'[Month])return calculate(sum('Values'[Value]),'Values'[Year]=2020,'Values'[Month]=m)Then create a matrix visual with the Month as rows. NOTHING as columns, and the two measures as values.Lastly create another measurediff = [2020]-[2019]and also add to the values area.May need cleanup for the scenario where 2020 values are blank - not sure what you want to do in such a case. Thank you, that worked! I read so many different ways of doing it I confused myself. I think your solution was the easiest way to go. Thanks again.
