cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 20192020Diff
1555444 
2666222 
3777555 
4888444 
5999333 
611150 
7222  
8333  
9444  
10555  
11666  
12777  

 

 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User V
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 = [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.
 

 

View solution in original post

2 REPLIES 2
Highlighted
Super User V
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 = [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.
 

 

View solution in original post

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.   

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

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