Table and relationship structure - year or date

[pat_energetics]

Hi all,

I'd be interested to learn from anyone's experience (good or bad) in applying data which only changes once per year to a broader model where other fact data tables may change daily. Consider the alternatives shown in the (simplified) example below, where YearDate contains a many to one relationship to unique DateKey in a calendar, but requires other DAX techniques to overcome the many-to-many relationship to the year. YearInt contains a many to one releationship to a unique YearTable, which in turn has a one to many relationship back down to the calendar (YearKey). 

The desired outcome for many of the downstream measures, and calculations between various tables in the model is that the "Price" for a particular "Commodity" applies for the entire year (all dates within that year) and not just on the 1/1/20XX. In the case of the YearDate alternative, any calculation which doesn't include all dates in the relationship back to the date table programmatically will return blank.

Any comments greatfully appreciated.

Table DataModel Alternatives

[austinsense]

Maybe something like this - I'm sure there's an easier way.  The key is to iterate over your date table so you return a value for every date.

IF ( HASONEVALUE (MasterDate_PBI[DateKey], SUMX(MasterDate_PBI, CALCULATE (YearIntData[Price]) ) )

[pat_energetics]

Thanks @austinsense - so it really doesn't matter if the price reference is contained in a table configured with a YearDate or a table configured with a YearInt ? 

So long as the (aggregation)x function iterates over the MasterDate table and the fact / reference table has some form of relationship to the MasterDate (either directly using datekey or indirectly using YearTable yearkey) ?

Would there be any performance differences / benefits in either approach ?

[v-sihou-msft]

@pat_energetics

According to your description, you just need to achieve a slow change dimension for Prodcut. So in my opionion, it's not necessary to have that YearDateData table. You can use Year() function get year part from date column when you apply filters in CALCULATE() functon. See:

http://geekswithblogs.net/darrengosbell/archive/2014/04/09/dax-ndash-joining-to-a-slowly-changing-di...

http://sqlblog.com/blogs/alberto_ferrari/archive/2011/02/09/powerpivot-and-the-slowly-changing-dimen...

If you really need that table, try to use LOOKUP() function to populate Price into a date based table with Year() function on column.

Regards,