Forum Discussion
Helper IICustom Date table based on date field in FACT table with hierarchy applied to datetime field
Hello all,
I don't mind where the date table is created be it in the SQL Server database, Power Query, etc. Wherever it makes the most sense.
I have a FACT table in SQL Server. There is a datetime field in the FACT table and there are duplicate dates in that table.
Option 1
Create a view of the FACT table that contains a unique list of datetime values and possibly all the other date fields I would need, namely:
Date (with no Time component)
Year (int)
Month (int)
Day (int)
MonthName (text)
Time
TimeHour (int)
TimeMinute (int)
Then, I would get the data into PowerBI, preferably by DirectQuery mode, but that doesn't appear to allow for marking the table as a Date table. Import mode doesn't fair much better in that Power BI thinks I have duplicates. DateTime is the selected field and it is unique. Date, Time, TimeHour, TimeMinute, Month, Day and MonthName are all not unique.
Ultimately, I would like to create a hierarchy from DateTime with all of the other fields. Then I want to create a line graph and put the year from the hierarchy in the Legend well, so that I will have two lines (one for each year) with the day along the x-axis. It is quite possible that I need a custom text field with Month and day combined or graph all the data but only show the month headings on the x-axis.
Option 2
Unfortunately, I can't seem to create this date table. I could calculate the start and end dates and populate each hour between the start and end dates but that would mean I would have to go back and ensure that this calculation is still accurate every time I add data to the FACT table. For now, I only have a single metric/value pair for each station and datetime value. I feel like this design pattern would require re-visiting each time a change was make and wouldn't have the robustness to keep working despite data additions to the FACT table.
Thanks in advance for you help,
R
Hi Riyaz999 ,
When you use the full Date column as a continuous axis in Power BI, it creates a single chronological timeline, resulting in one long line across all years rather than overlapping lines. This is the expected behavior. If you want to compare multiple years on the same January - December scale, you will need a normalized axis like DayOfYear or a Month-Day index. This method aligns all years onto one seasonal timeline and allows for separate lines for each year. A continuous date axis is not designed to overlay years on top of each other.
16 Replies
- cengizhanarslan
Super User
A) Create a Date dimension (daily grain, unique)
In SQL (best for DirectQuery), create a calendar table/view with one row per date:
DateKey (date)
Year, MonthNo, MonthName, Day, etc.
Then relate:
DimDate[Date] (1) → Fact[Date] (*)
Where Fact[Date] is the date part only of your datetime.
If your fact only has DateTime, add a persisted computed column or view column:
CAST(Fact.DateTime AS date) as [Date]B) Create a Time dimension (optional, only if you truly need hour/min)
If you need slicing by hour/minute, create a separate small time table:
TimeKey (time)
Hour, Minute
Relate:
DimTime[Time] (1) → Fact[Time] (*)
Where Fact[Time] is:
CAST(Fact.DateTime AS time(0)) as [Time]This avoids trying to make one giant “datetime dimension” that fails uniqueness rules.
Don’t build a hierarchy off the raw DateTime column for this. Instead:
Use DimDate hierarchy: Year > Month > Day
Use DimTime separately (Hour > Minute) if needed
This is the standard star-schema approach and it’s robust as new data arrives (no manual maintenance). Also about legends, you could use those hierarchies in X-axis instead only using Month level grain for sure.
Riyaz999Thanks for your response cengizhanarslan,
I really like how this normalized design reduces the data footprint. I have created the tables as follows, as per your suggestion:
DimDate
- Date
- Day
- Month
- MonthName
- Year
DimTime
- Time
- TimeHour
- TimeMinute
DimStation
- StationId
- StationName
- Elevation
FactData
- Date
- DateTime
- Metric
- StationId
- Time
- Value
Relationships
DimDate[Date] (1) -> FactData[Date] (*)
DimTime[Time] (1) -> FactData[Time] (*)
DimStation[StationId] (1) -> FactData[StationId] (*)
Hierarchies
DimDate table -> DateHierarchy (Year, Month, MonthName, Day)
DimTime table -> TimeHierarchy (Time, TimeHour, TimeMinute)
Visual (Line Chart)
x-axis - ?
y-axis - Sum of FactData[Value]
legend - DateHierarchy[Year]
What should I put in the x-axis well so that I get the days of the year excluding the year (i.e., Jan 1 through Dec 31) and a different line (diff colour for each) for each year? Right now, no matter what I have done, I get Jan 1, 2024 through Jan 21, 2026 as one continuous data set.
Thanks in advance,
R- cengizhanarslan
Put your day value in x-axis and from the visual settings change type to "Categorical".
- Riyaz999
Hello again cengizhanarslan,
I had set the x-axis as categorical but it still didn't represent what I was hoping for. I made the following updates:
and now, it is starting to look like what I was hoping.
so thanks for all of your help that got me to this point. Here is a summary of the solution based on your helpful feedback:
1) Created the two separate dim tables in SQL based on FACT table, one for date and one for time
2) In the PowerBI Model View, each Dim table was linked directly to the fact table
3) A date hierarchy was created in the DimDate table that looks as follows:4) The date hierarchy was dragged into the x-axis well but Year and Month were removed
5) The date hierarchy was dragged into the Legend well but all fields were removed with the exception of Year
6) Since I have hourly data, the Average value (for a given day) is represented in the y-axis well
Remaining issues:
1) Zoom level should be set such that the full graph is visible without a horizontal scrollbar. I would want a zoom slider that allows for showing more detail but the default should show the fill graph and I don't see where I can set this. I am also only seeing a slider on the y-axis which is likely due to the date hierarchy being used on the x-axis
2) My data seems to start from April 1st, where I would want the left most point to be January 1st. Not sure why the chart is defaulting to what appears to be a fiscal calendar???
Regards,
R
- Riyaz999
If a picture helps to visualize the Model:
