PDA

View Full Version : Calendar table but with times of the day



Hypnopoison
2016-10-29, 01:32 PM
Hi everyone

I'm new and I was hoping I could ask for some help please, as I'm a bit stuck :-(
I am attempting to make a Telesales Call Centre monitoring Power Pivot file, which will monitor outbound call volumes against target, by the hour.

I'm able to link to my source data ok, but I understand that I need to add a calendar table in my data model, and I have found many tutorials to show me how to create one, but what none of them seem to include is how I include the times for each of the days in the calendar table.

Ideally for each date listed in the table, I'd have the corresponding hours of the day, I was thinking of using 30 minute increments (but not sure if it would be better to include set increments or every minute of every day), to enable me to compare actual outbound call volume against the hourly targets.

Does anyone know how to to create such a calendar/time table using either Power Pivot or Power Query?
I've attached a quick example file to show what I'd need to create but including every day from 01-01-2016 for the next few years.

I could create this manually in Excel - but that might take quite a while and I thought that there has to be a more efficient way to do this.
Any help would very much appreciated.

Thanks
Regards
Debbie

Owen Auger
2016-10-31, 02:32 AM
Hi Debbie,

A couple of points:


Quoting The Definitive Guide to DAX (p 443): "You should always split date and time in two separate columns."
You would then have separate Date & Time columns in your fact table (don't keep the DateTime column), and two separate dimension tables for Date & Time. This avoids having a single DateTime column with very high cardinality, and means you can still use time intelligence functions with your Date table.
You can construct a Time table using whatever granularity you like, such as 30 minutes (48 values). You can build this table however you like. Some simple M code would be:

#table(
type table [ Time = time ],
let
TimeCardinality = 48
in
List.Transform (
{ 0..TimeCardinality-1 },
each { Time.From ( _/TimeCardinality ) }
)
)

Hypnopoison
2016-11-08, 12:17 PM
Hi Owen, thanks for replying. Do you know what the query would be to get all those times against everyday from 2016 to say 2025 (and all the other dates varieties in Calendar Code):

My current calendar code looks like this:
let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date])),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date)
in
InsertWeekEnding,
#"Invoked FunctionCreateDateTable" = CreateDateTable(#date(2016, 1, 1), #date(2025, 12, 31), null)
in
#"Invoked FunctionCreateDateTable"

Owen Auger
2016-11-11, 04:50 AM
Hi Debbie,
You should not combine the Calendar & Time tables.
Your Calendar table is fine as is, and you should have a separate Time table (created for example with code in my earlier post).
Then ensure there are separate Date & Time columns in your fact table, and have separate relationships to your Date & Time tables.

Hypnopoison
2016-11-11, 09:12 AM
Hi Owen, brilliant - thanks very much for your help :-)