General Dates and DM Query

[#1]

Hello Experts,

I am working on an application, where in Calendar Table the datekey is coming from SQL and its like 77463 format as its a key and in the same master calendar I have the YearMonth dimension in the format of 2011-07. I have an external excel sheet with below data which I am trying to link to my data model master calendar. I am trying something like Join load any suggestions please?

YearMonth	Expenses
2011-03	8,114.20
2011-04	2,859.15
2011-05	5,222.29
2011-06	7,237.46
2011-07	4,979.86

After I load this data, it shows me as 42350 instead of the series of numbers as 77463 and I tried changing the data format in Excel to DD/MM/YYY but that doesnt help.

I want to link this data to my master calendar for the expenses to use across the application any help please..

Thanks,

Mady

[#2]

Hello Mady,

if I got you right, your master cal is based on days. Its key is a number representing one day - let's call it a DayDate. Your excel-data comes with a date based on month as the expenses are per month. So I would transform the YearMonth into a DayDate concatenating a '-01' to YearMonth. May be you have to calculate(transform, reformat, ....) this DayDate into the according number of your master calender key. After this you have a valid key to link your excel data (now based on a DayDate) to your master cal using eg. left join.

After this all your reports sholud work as expected.

HtH

Roland