Forum Discussion
Advocate IIIData view - date display formats
Advocate IIIAnonymous 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
- KarlinOz
Thank you Anonymous and is 'YourFactTable' literally 'YourFactTable' ? or should that be the name of the table I am wanting to join to?
- KarlinOz
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