topic Calculate the difference between quantities per month over two years in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-difference-between-quantities-per-month-over-two/m-p/1147630#M17375
<P>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. </P><P> </P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Month </TD><TD>2019</TD><TD>2020</TD><TD>Diff</TD></TR><TR><TD>1</TD><TD>555</TD><TD>444</TD><TD> </TD></TR><TR><TD>2</TD><TD>666</TD><TD>222</TD><TD> </TD></TR><TR><TD>3</TD><TD>777</TD><TD>555</TD><TD> </TD></TR><TR><TD>4</TD><TD>888</TD><TD>444</TD><TD> </TD></TR><TR><TD>5</TD><TD>999</TD><TD>333</TD><TD> </TD></TR><TR><TD>6</TD><TD>111</TD><TD>50</TD><TD> </TD></TR><TR><TD>7</TD><TD>222</TD><TD> </TD><TD> </TD></TR><TR><TD>8</TD><TD>333</TD><TD> </TD><TD> </TD></TR><TR><TD>9</TD><TD>444</TD><TD> </TD><TD> </TD></TR><TR><TD>10</TD><TD>555</TD><TD> </TD><TD> </TD></TR><TR><TD>11</TD><TD>666</TD><TD> </TD><TD> </TD></TR><TR><TD>12</TD><TD>777</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><P> </P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV>Mon, 08 Jun 2020 21:50:08 GMTDan_B2020-06-08T21:50:08ZCalculate the difference between quantities per month over two years
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-difference-between-quantities-per-month-over-two/m-p/1147630#M17375
<P>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. </P><P> </P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Month </TD><TD>2019</TD><TD>2020</TD><TD>Diff</TD></TR><TR><TD>1</TD><TD>555</TD><TD>444</TD><TD> </TD></TR><TR><TD>2</TD><TD>666</TD><TD>222</TD><TD> </TD></TR><TR><TD>3</TD><TD>777</TD><TD>555</TD><TD> </TD></TR><TR><TD>4</TD><TD>888</TD><TD>444</TD><TD> </TD></TR><TR><TD>5</TD><TD>999</TD><TD>333</TD><TD> </TD></TR><TR><TD>6</TD><TD>111</TD><TD>50</TD><TD> </TD></TR><TR><TD>7</TD><TD>222</TD><TD> </TD><TD> </TD></TR><TR><TD>8</TD><TD>333</TD><TD> </TD><TD> </TD></TR><TR><TD>9</TD><TD>444</TD><TD> </TD><TD> </TD></TR><TR><TD>10</TD><TD>555</TD><TD> </TD><TD> </TD></TR><TR><TD>11</TD><TD>666</TD><TD> </TD><TD> </TD></TR><TR><TD>12</TD><TD>777</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><P> </P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV>Mon, 08 Jun 2020 21:50:08 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-difference-between-quantities-per-month-over-two/m-p/1147630#M17375Dan_B2020-06-08T21:50:08ZRe: Calculate the difference between quantities per month over two years
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-difference-between-quantities-per-month-over-two/m-p/1147774#M17384
<P>Not proud of this one but here goes:</P><P> </P><P>Create two measures</P><DIV><DIV><SPAN>2019 = </SPAN></DIV><DIV><SPAN>var m = SELECTEDVALUE('Values'[Month])</SPAN></DIV><DIV><SPAN>return calculate(sum('Values'[Value]),'Values'[Year]=2019,'Values'[Month]=m)</SPAN></DIV><DIV> </DIV><DIV><DIV><DIV><SPAN>2020 = </SPAN></DIV><DIV><SPAN>var m = SELECTEDVALUE('Values'[Month])</SPAN></DIV><DIV><SPAN>return calculate(sum('Values'[Value]),'Values'[Year]=2020,'Values'[Month]=m)</SPAN></DIV><DIV> </DIV><DIV><SPAN>Then create a matrix visual with the Month as rows. NOTHING as columns, and the two measures as values.</SPAN></DIV><DIV> </DIV><DIV>Lastly create another measure</DIV><DIV><DIV><DIV><SPAN>diff = [2020]-[2019]</SPAN></DIV><DIV> </DIV><DIV><SPAN>and also add to the values area.</SPAN></DIV><DIV> </DIV><DIV><SPAN>May need cleanup for the scenario where 2020 values are blank - not sure what you want to do in such a case.</SPAN></DIV></DIV></DIV><DIV> </DIV></DIV></DIV></DIV><P> </P>Tue, 09 Jun 2020 01:31:08 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-difference-between-quantities-per-month-over-two/m-p/1147774#M17384lbendlin2020-06-09T01:31:08ZRe: Calculate the difference between quantities per month over two years
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-difference-between-quantities-per-month-over-two/m-p/1147992#M17394
<P>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. </P>Tue, 09 Jun 2020 04:16:45 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-difference-between-quantities-per-month-over-two/m-p/1147992#M17394Dan_B2020-06-09T04:16:45Z