Aggregation by month connect to date dimension

[Don-Bot]

So this may be a beginner type question but I am having issues with this.

I am creating an aggregation import table that I want to be by month.  However, my calendar table is actually by date time for the fact table.  

How can I connect it so users can use the date dimension table for the calendar yet it fires off the aggregation.  

I tried putting a Month Date in the dimension and then connect that to my aggregation it yells and says the aggregation can't have a bi-direction filter on it.  

[3CloudThomas · Accepted solution]

It is bi-directional becuase there are 'many' rows in the date dimension for a single month. You would need to do 1 of 2 things (there might even be more than 2 🙂
1. Create a view of Date Dimension to only have one row per month and join on view
2. In the aggregation table, add a column for the first day of month (or last day of month) and join on date dimension by this new column (1/1/2021 or 1/31/2021)

[Clampazzo]

Hi @3CloudThomas I'm attempting #2 above and it's not working.  I'm unsure as to why.  

I have the typical detail table and aggregation table.  Detail is by datetime and aggregation is by month.  

I created a custom id column that is the BOM I then connected that to the datetime ID on the Date Time dimension in the model.

I then did a manage aggregation and pointed the new custom field in aggregation to a group by on the main fact table datetime ID.  

For whatever reason it's not hitting the aggregation when I try to use month/year in date time table....

The reason in dax studio is "no column mapping"...

Any ideas as to why?

[Don-Bot]

Hi @3CloudThomas , I was looking at this again and have a follow up question.  

If I went with option 1 do you have any recommendations on how I could reconcile what would end up being 2 date dimensions?

1 would be the calendar date dimension that has every day in it.  The 2nd would be the monthly one you suggested above.  

Thanks

[Don-Bot]

I did exactly what your #2 suggestion is.  Seems to work like a charm.

Thanks!

[3CloudThomas]

Awesome!!!