Forum Discussion
Helper IApply Custom Date Hierarchy to Model
I'll try to give Kudos accordingly to all those who have helped but ultimately a combination of fixes were necessary:
1. Ensure there exists a Date table within the model that includes its own hierarchy with "Week" within it.
2. Separate the table of data into 2 tables, one for opened items and one for closed items
3. Ensure each table's Date column is either formatted as "Date" (rather than DateTime) or else create a duplicate column that is defined as Date only
4. Connect the two tables' "Date" formatted Date columns to the Date table within the model
5. Build the Visualisation, using the Count of the date only date columns of the two data tables as the column values, and the Date hierarchy as the shared axis - the data has come out perfectly.
Super UserI think you are having trouble becuase the [Open Date] column in your 'Problems in the last 12 weeks data' table is a date/time. It is not able to find a matching entry in the Date column because of the time portion of the filed. You can convert it to just the date using INT in a new column.
Open Date Only = INT ( 'Problems in the last 12 weeks data'[Open Date] )Then join that into your date table.
- PantherusNZ
Hi, Where would I enter that bit of code?
I tried creating the date-specific column in data transforming to get everything aligned but it still refuses to acknowledge that connection.
- jdbuchanan71
In the table view you would add it as a new column.
- PantherusNZ
Aha! OK, thank you - created the column.
I've recreated the Relationship to the custom date table and...no hierarchy.