Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Calculate the difference between quantities per mo...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

Dan_B

New Member

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-08-2020
02:50 PM

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 |

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

lbendlin

Super User V

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-08-2020
06:31 PM

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.

2 REPLIES 2

Highlighted

lbendlin

Super User V

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-08-2020
06:31 PM

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.

Highlighted
##

Dan_B

New Member

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-08-2020
09:16 PM

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.

Top Solution Authors

User | Count |
---|---|

53 | |

50 | |

18 | |

13 | |

11 |