Hourly Forecast

[Anonymous]

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. 

[Greg_Deckler]

@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.

[Anonymous]

@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?

[Greg_Deckler]

@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.

[Anonymous]

Any luck @Greg_Deckler  😁? I have been trying different approaches, but the other than a very inaccurate daily forecast i haven't gotten. 

[Greg_Deckler]

@Anonymous - No data to test with. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.