Forum Discussion
% change (month-over-month) crashes when aggregating
I've been working on this for a couple days, only coming to half a solution. I'm hoping one of the many gurus here can look at my file and tell me what I'm doing wrong. I'm not afraid of doing the work, just at a loss as to how to make the desired result.
Problem statement: given a table of sales data (see format below), present a graph like the one on powerbi.tips (month-on-month % change) -- well, it could be any date range for that matter...
To date, I've tried a variety of solutions, including attempting the "second table with dates" option, and have had no luck creating a % change graph. I have been able to create a table that shows the % change, but only when the rows are set day-by-day (see image at the bottom). As soon as I aggregate, either with bins or with date heirarchy, the "previous month" stops working, % change goes to infinity, and any graph derived from the same becomes a flat line.
I'm hoping one of your smart folks can tell me where I've gone awry. Alternately, if one of you brave souls wants to interact more directly, please send me a PM and I'll email you.
Thanks, Chris S.
Example File
Problem Month-on-Month Data file
Code Sample (all measures)
Current Monthly Sales = CALCULATE([Total Unit Sales],PARALLELPERIOD('product-sales-by-day'[date],0,MONTH))
Prior Month Sales = CALCULATE([Total Unit Sales], PREVIOUSMONTH('product-sales-by-day'[date]))
% Change = ([Current Monthly Sales]-[Prior Month Sales])/[Prior Month Sales]
Example output
Data Sample
| date | sale_id | units_sold | item_id |
| 1/1/2015 0:00 | 10 | 1365 | 134 |
| 1/3/2015 0:00 | 17 | 1680 | 310 |
| 1/4/2015 0:00 | 56 | 10290 | 134 |
| 1/4/2015 0:00 | 250 | 186270 | 142 |
| 1/4/2015 0:00 | 337 | 196140 | 149 |
| 1/4/2015 0:00 | 273 | 191100 | 150 |
| 1/4/2015 0:00 | 112 | 24255 | 249 |
| 1/4/2015 0:00 | 109 | 42000 | 257 |
| 1/4/2015 0:00 | 204 | 46988 | 289 |
| 1/4/2015 0:00 | 597 | 505995 | 290 |
| 1/4/2015 0:00 | 40 | 9660 | 310 |
| 1/4/2015 0:00 | 3 | 140 | 314 |
| 1/4/2015 0:00 | 684 | 71820 | 332 |
| 1/6/2015 0:00 | 1076 | 877695 | 149 |
| 1/6/2015 0:00 | 730 | 533085 | 150 |
| 1/6/2015 0:00 | 362 | 152670 | 249 |
| 1/6/2015 0:00 | 225 | 72450 | 257 |
| 1/6/2015 0:00 | 549 | 119263 | 289 |
| 1/6/2015 0:00 | 1756 | 1545915 | 290 |
| 1/6/2015 0:00 | 192 | 43995 | 310 |
| 1/6/2015 0:00 | 6 | 210 | 314 |
| 1/6/2015 0:00 | 104 | 11025 | 332 |
| 1/7/2015 0:00 | 118 | 19845 | 134 |
| 1/7/2015 0:00 | 509 | 381780 | 142 |
2 Replies
- Vvelarde
Community Champion
Hi, My propose to solution is:
1. Add a Calendar Table
From Modeling - New Table
Calendar=CalendarAuto()
2. The measures
Current Monthly Sales = CALCULATE([Total Unit Sales])
Prior Month Sales = CALCULATE([Total Unit Sales]; PREVIOUSMONTH('Calendar'[date]))
3. The Visual
Thanks very much for the help. Your fix was just what I needed. I went back in with some calendar table help from this site (Calendar Table in Two Steps), but I had to replace the semicolons with commas in my version of PowerBI.
Also, I realized my graphs were going to infinity because a single row was in error. This code fixed it:
% Change =
// the % change variable can't be infinity or it throws off the graphs
if( ISERROR(([Current Monthly Sales]-[Prior Month Sales])/[Prior Month Sales]),
BLANK(),
([Current Monthly Sales]-[Prior Month Sales])/[Prior Month Sales]
)
The final result is just what I needed.Solution file is here for others to use: Solution File
