Using Calendar table to summarize items by month

[Riggan]

Hi. I Have two tables one is just simple calendar table with unique dates, and one is a table with two columns Date and Id. Date is not unique. I want to build a simple barchart using these two tables. As X axis I vant to use dated from Calendar table, and for values I want to use count of Id. The goal to create chart which showing count of Id monthly. I have a sample file

https://limewire.com/d/G9nvD#PAWsLynnP7 

I've connected two tables this way 

And my settings for the chart looks like this

But result shows just one bar. But I need month bars

Please point me my mistake

[MasonMA]

Hi, @Riggan, @johnt75 

Could one of you briefly explain how this was fixed? i'm also interested into this topic and tried your solution @johnt75 on my end. but the Calendar table is still not filtering the Item table. The Count of Item can only be shown when i select 'Show item with no data' under the X-axis and the bar is apprearing on blank date, which means the Calendar table is not filtering Item table. First i thought it was the DAX that generated Calendar table mismatches the range because the date in Items table are all in 2022, but after i adjusted the DAX that generated Calendar table to year 2022, the bar is still not showing correctly. (all Items[Date] and Calendar[Date] are of type date)

i believe the correct one should look like below. this was done by selecting Date from Item table. but this is not what @Riggan was asking. 

super weird.. 

thanks

Mason 

[johnt75]

The dates in the second image show 2002, not 2022. Try using CALENDARAUTO to generate your calendar table, that will automatically create dates covering the entire period of your data.

[johnt75 · Accepted solution]

Make sure that Items[Date] is of type date, not text or datetime. Check in Power Query too that it is a date, as if it is a datetime in power query then it will not link correctly to the date column in your calendar.