Forum Discussion
Missing data points for moving months
We are trying to calculate 12 moving months sum of a metric. The bar chart below has all the values from Apr 2019 to May 2020, however complete data points for May 2020 is missing.
We dont have any value for the blue metric of May 2020 in our source, but there should be a value on the chart since it is summing up the values for the last 12 moving months. We are expecting it to show some value for May 2020, by considering the values present in the previous months. Any idea why the value is not showing up in the visual or any workarounds/suggestions?
Please find the sample data for the same. It has the values from June 2019 - June 2020.
| DATE | Category | Weight |
| 6/1/2019 0:00 | Green | 980130.5 |
| 6/1/2019 0:00 | Yellow | 86004.25 |
| 6/1/2019 0:00 | Blue | 3098015 |
| 7/1/2019 0:00 | Green | 1080521 |
| 7/1/2019 0:00 | Yellow | 430141.6 |
| 7/1/2019 0:00 | Blue | 3939509 |
| 8/1/2019 0:00 | Green | 1111768 |
| 8/1/2019 0:00 | Yellow | 235895.3 |
| 8/1/2019 0:00 | Blue | 3508745 |
| 9/1/2019 0:00 | Green | 2278745 |
| 9/1/2019 0:00 | Yellow | 950222.7 |
| 9/1/2019 0:00 | Blue | 1498396 |
| 10/1/2019 0:00 | Green | 1001085 |
| 10/1/2019 0:00 | Yellow | 965401.9 |
| 10/1/2019 0:00 | Blue | 1368455 |
| 11/1/2019 0:00 | Green | 797024.9 |
| 11/1/2019 0:00 | Yellow | 844521.1 |
| 11/1/2019 0:00 | Blue | 2903498 |
| 12/1/2019 0:00 | Green | 995918.8 |
| 12/1/2019 0:00 | Yellow | 675661.9 |
| 12/1/2019 0:00 | Blue | 2629555 |
| 1/1/2020 0:00 | Green | 507012.1 |
| 1/1/2020 0:00 | Yellow | 472983.7 |
| 1/1/2020 0:00 | Blue | 2780379 |
| 2/1/2020 0:00 | Green | 1108798 |
| 2/1/2020 0:00 | Yellow | 189126.4 |
| 2/1/2020 0:00 | Blue | 2851289 |
| 3/1/2020 0:00 | Green | 1376035 |
| 3/1/2020 0:00 | Yellow | 473091.9 |
| 3/1/2020 0:00 | Blue | 1914190 |
| 4/1/2020 0:00 | Green | 930264.2 |
| 4/1/2020 0:00 | Yellow | 278974.8 |
| 4/1/2020 0:00 | Blue | 1147147 |
| 5/1/2020 0:00 | Green | 2348329 |
| 5/1/2020 0:00 | Blue | 1264809 |
| 6/1/2020 0:00 | Green | 1075077 |
| 6/1/2020 0:00 | Yellow | 670088 |
| 6/1/2020 0:00 | Blue | 2835990 |
Hi Anonymous ,
Please refer to my .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous , Are you using date table? In this you should use date table and month year should come from date table
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi Anonymous ,
Please refer to my .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
