Forum Discussion
Data view - date display formats
I thought you had a date table.
Try this.
1. Create a calendar(date) table from the transactiondate in fact table.
2. Join the Calendar Table and the Fact Table on the datekeys.
3. Create the new column in the fact table
4. Set the sort by column to TransactionDate in Fact table
this should work
Cheers
CheenuSing
Anonymous are you sayng to create a new table from the column DateTime in my 'Correspondance Register' table? I have not worked out how to do that yet. I select the DateTime column then hit New Table on the icon bar but then I am at a loss as to where to go from there. I guess I will try Googling that. I had tried Google for it in the past but didn't find a good instruction.
Creating a Calendar / Date table is as simple as writing
Calendar = CALENDAR (MINX('YourFactTable', [YourFactTableDate]),MAXX('YourFactTable', [YourFactTableDate]))
This will create the table Calendar in your data model with Date as a column.
Cheers
CheenuSing
Thank you Anonymous and is 'YourFactTable' literally 'YourFactTable' ? or should that be the name of the table I am wanting to join to?
I feel like a baby, having to ask for each step, sorry.
I created the Calendar table, created a relationship between the 'Fact Table' and Calendar on the two date fields. The Calendar is the One side and the 'Fact Table' the Many. In my 'Fact Table' I created a new column: DateIndex = RELATED('Calendar'[Date]) which created ok but it is empty, no row has any data in it. What am I likely to have done wrong?