Forum Discussion
Data view - date display formats
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?
No worries. It is part of the learning process.
Folow the steps as follows
1. Create a Calendar / Date table is as
Calendar = CALENDAR (MINX('YourFactTable', [DateTime]),MAXX('YourFactTable', [DateTime]))
This will create the table Calendar in your data model with Date as a column.
2. Go the Modelling Tab in the power bi.
3. Go to Manage Relationship
4. Create relationship between the Date of Calendar Table and date column [DateTime] of YourFactTable
5. In YourFactTable create the column (which you might have already done)
DayDateTime = FORMAT([DateTime], "ddd DD/MM/YYYY hh:mm")
6. Now set the Sort order for DayDateTime as [DateTIme] column of yourfactable.
7. Now plot the DayDateTime it should be in the proper order.
If it works please accept it as a solution and also give KUDOS.
Cheers
CheenuSing
Thank you for your step by step instructions Anonymous, I have followed them however still at step 6 I ge the error: