Time Intelligence with Monthly Data Model AND Day by Day

[MondayApril]

Below is an example I am using:

"I'm working on my first BI project and have a requirement to provide sales information by salesrep.  the source data is at a month granularity i.e. sales by rep for Jan 2018, Feb 2018, Mar 2018, etc.  I have 3 years of data like this.  I also have a pre-built calendar table in our source environment that I'm planning on loading in - this table is keyed on a date field with 1 entry for each day starting back in 2010 all the way up through 2040.

My understanding when creating a relationship on a calendar table to a fact table is to make sure the key is a date field.

Since my sales table is monthly and my calendar table is daily my thought is to create a 'date' field in the sales table for the 1st of each month i.e. 01/01/2018, 02/01/2018, etc.  I can then join the 2 tables to take advantage of all the time intelligence functionality.

Is this the correct approach when dealing with monthly data?  Does it make more sense for the date value to be the LAST DAY of each month?  Or can I create a Year/Month field i.e. YYYYMM and join the 2 tables on that?"

What does one do in this situation when you have both Monthly aggregate and daily detail? My time intelligence shows problems filtering.

[v-yiruan-msft]

Hi @MondayApril ,

How to deal with your model tables(like add year month field or something else), it depends on the actual scenario and your expected result. In order to make a better understanding on your requirment and give you a suitable solution, could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards