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
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors