Forum Discussion
Hourly Forecast
Hello all,
Currently I'm working on a report in which i need to make a 24 hour forecast (per hour).
I could use the timeseries visuals en use their forecasting methods, but i need them to be in a table (or display them in a bar chart with the predictions for today).
The data that i have at my disposal:
DateTable = Just your typical calendar table with dates, year, week, month etc.
Timetable = Just a table with all seconds of the day, with labels such as the hour it was in, the minute etc.
The incoming messages table = Each row represent a message:
- MessageID
- Date
- Time
- Team
I have about 3 years of data, so sharing it won't be an option. But ideally i would want to combine each date from the date table, with 1 row per hour of the day and the total messages in that hour. And than the forecast for today. No need for forecasting any further than that.
5 Replies
Anonymous - Well you could use GENERATE to join your date and time tables. Combine your Date and Time columns into a Date Time column. You could then relate that table to your messages table. Probably need to combine your Date and Time columns into a single Date Time column to match your GENERATE table. The rest should be pretty easy after that.
Hard to really say though without some sample data to play with.
Greg_Deckler Trying to upload a pbix file, but it doesn't let me 😪
Here is the code for my timetable:
let Source = {1..86400}, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "TimeKey"}}), #"Added Time Column" = Table.AddColumn(#"Renamed Columns", "Time", each Time.From("00:00:00") + #duration(0,0,0,[TimeKey])), #"Inserted HourKey" = Table.AddColumn(#"Added Time Column", "HourKey", each Time.Hour([Time]), type number), #"Inserted MinuteKey" = Table.AddColumn(#"Inserted HourKey", "MinuteKey", each Time.Minute([Time]), type number), #"Inserted SecondKey" = Table.AddColumn(#"Inserted MinuteKey", "SecondKey", each Time.Second([Time]), type number), #"Added Hour" = Table.AddColumn(#"Inserted SecondKey", "Hour", each Time.ToText(#time([HourKey],[MinuteKey],[SecondKey]),"HH")), #"Added Minute" = Table.AddColumn(#"Added Hour", "Minute", each Time.ToText(#time([HourKey],[MinuteKey],[SecondKey]),"hh:mm")), #"Added Second" = Table.AddColumn(#"Added Minute", "Second", each Time.ToText(#time([HourKey],[MinuteKey],[SecondKey]),"hh:mm:ss")), #"Changed Type to Locale Time" = Table.TransformColumnTypes(#"Added Second",{{"Time", type time}},"en-us"), #"Added HourText" = Table.AddColumn(#"Changed Type to Locale Time", "HourText", each [Hour] & ":00"), #"Added Daypart" = Table.AddColumn(#"Added HourText", "Daypart", each if [HourKey] >= 0 and [HourKey] < 6 then "Night" else if [HourKey] >= 6 and [HourKey] < 12 then "Morning" else if [HourKey] >= 12 and [HourKey] < 18 then "Afternoon" else if [HourKey] >= 18 then "Evening" else "Other"), #"Added Hour:Minutes" = Table.AddColumn(#"Added Daypart", "Hour:Minutes", each [Hour]& ":00") in #"Added Hour:Minutes"Date table:
let StartDate = #date(2019,1,1), EndDate = DateTime.Date(DateTime.LocalNow()), NumberOfDays = Duration.Days( EndDate - StartDate ), Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DateKey"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"DateKey", type date}}), #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([DateKey]), Int64.Type), #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([DateKey]), Int64.Type), #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([DateKey]), type text), #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([DateKey]), Int64.Type), #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([DateKey],0), Int64.Type), #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([DateKey],0), Int64.Type), #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([DateKey]), Int64.Type), #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([DateKey],0), Int64.Type), #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([DateKey]), Int64.Type), #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([DateKey]), type text), #"Inserted Week Number" = Table.AddColumn(#"Inserted Day Name", "Week Number", each Date.WeekOfYear([DateKey], Day.Sunday), Int64.Type), #"Added Custom" = Table.AddColumn(#"Inserted Week Number", "min30", each Date.AddDays([DateKey], -30)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "min60", each Date.AddDays([DateKey], -60)), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"min30", type date}, {"min60", type date}}) in #"Changed Type1"And for the messages sample data i created an excel file with:
ID: list of 1 tm 2000
Date: =RANDBETWEEN(DATE(2019,1,1), DATE(2020,8,19))
Time: =TEXT(RAND(),"HH:MM:SS")
Team: =CHAR(RANDBETWEEN(65, 85))
Is this something you can use?Anonymous - Yeah, I can give that a shot. If you don't have PBIX upload rights, most people just use OneDrive or Box to share it out and post a link.
