cancel
Showing results for
Did you mean:
Highlighted
New Member

## 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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Super User V

## Re: Calculate the difference between quantities per month over two years

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 measure
diff = -

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 2
Highlighted Super User V

## Re: Calculate the difference between quantities per month over two years

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 measure
diff = -

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.

Highlighted
New Member

## Re: Calculate the difference between quantities per month over two years

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.

Announcements #### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge! #### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (850)