Missing data points for moving months

[Anonymous]

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

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@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.

[Anonymous]

We have posted the sample data for your reference. Please let us know.