cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

Time Intelligence with Monthly Data Model

Hello -

 

BI Newbie here and probably something very simple but I can't find a definitive answer so my apologies in advance ...

 

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?

 

Again, this sounds simple but it seems like there's multiple ways this could be accomplished.

 

Thanks in advance.

2 ACCEPTED SOLUTIONS
Solution Sage
Solution Sage

Sounds like the right approach. 

View solution in original post

Super User I
Super User I

Agree, I routinely create models with monthly transactional data, and simply follow a first-of-the-month or last-of-the-month date convention with dates in the fact table (can't see any reason to prefer one over the other), related to a standard daily calendar table. 

 

As long as reports don't filter below the month level, it works just fine with time intelligence functions.

 

There are methods to allocate monthly data to days (some ideas e.g. here) but there is no point in doing this if your underlying granularity is monthly.


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Can you build a date table without the day grain...   and time intelligence functions still work?

Regular Visitor

Hello -

 

BI Newbie here and probably something very simple but I can't find a definitive answer so my apologies in advance ...

 

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?

 

Again, this sounds simple but it seems like there's multiple ways this could be accomplished.

 

Thanks in advance.

Super User I
Super User I

Agree, I routinely create models with monthly transactional data, and simply follow a first-of-the-month or last-of-the-month date convention with dates in the fact table (can't see any reason to prefer one over the other), related to a standard daily calendar table. 

 

As long as reports don't filter below the month level, it works just fine with time intelligence functions.

 

There are methods to allocate monthly data to days (some ideas e.g. here) but there is no point in doing this if your underlying granularity is monthly.


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

Solution Sage
Solution Sage

Sounds like the right approach. 

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors