Formula for calendar table that advances not by day, but by hour or minute, etc?

[mmace1]

Calender Table = CALENDAR(DATE(2006,01,01),DATE(year(now()),month(now()),day(now())))

^ That would make a calender table from 1/1/2006 to the present, advancing day by day. 

What if one wants to advance hour by hour, or even minute by minute, though? 

Thanks, 

[mmace1]

I found a solution that worked for me here:  It doesn't involve a different calender table, but does allow me to calculate the working hours between two events: 

https://community.powerbi.com/t5/Desktop/Calculate-Date-and-Time-difference-considering-the-weekends...

[v-caliao-msft]

@mmace1,

You could create date table and time table. Please use the Power query below.

let CreateTimeTable = () as table =>
 let
 // Similar to our CreateDateTable script, we start with the smallest unit of the dimension, minute
 // There are a fixed number of minutes in a day, so no need for parameters here
 // 525,600 minutes divided by 365 days in a year = 1440 minutes in a day. 
 // Who says we never learn from Broadway musicals?
 MinuteCount = 1440,
 // Now create a Time type list for a total of 1440 minutes, incrementing one minute at a time
 Source = List.Times(#time(0, 0, 0),MinuteCount, #duration(0,0,1,0)),
 // Turn that list into a one column table
 TableFromList = Table.FromList(Source, Splitter.SplitByNothing()), 
 // Change that table's one column to type Time
 ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type time}}),
 // Rename column to Time
 RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Time"}}),
 // Start inserting columns for each unit of time to represent in the dimension
 InsertHour = Table.AddColumn(RenamedColumns, "Hour", each Time.StartOfHour([Time])),
 InsertMinute = Table.AddColumn(InsertHour, "Minute", each Time.Minute([Time])),
 ChangedTypeHour = Table.TransformColumnTypes(InsertMinute,{{"Hour", type time}}),
 // Creating levels in the hierarchy that might be useful for reporting. Omit if not useful to yours
 InsertQuarterHour = Table.AddColumn(ChangedTypeHour, "Quarter Hour", each if [Minute]<15 then [Hour] else if [Minute] < 30 then Value.Add([Hour],#duration(0,0,15, 0)) else if [Minute] < 45 then Value.Add([Hour],#duration(0,0,30, 0)) else Value.Add([Hour],#duration(0,0,45, 0))),
 ChangedTypeQtrHr = Table.TransformColumnTypes(InsertQuarterHour,{{"Quarter Hour", type time}}),
 ReorderedColumns = Table.ReorderColumns(ChangedTypeQtrHr,{"Time", "Hour", "Quarter Hour", "Minute"}),
 InsertHourNumber = Table.AddColumn(ReorderedColumns, "Hour Number", each Time.Hour([Time])),
 NextHour = Table.AddColumn(InsertHourNumber, "Next Hour", each Value.Add([Hour],#duration(0,1,0, 0))),
 NextQuarterHour = Table.AddColumn(NextHour, "Next Quarter Hour", each Value.Add([Quarter Hour],#duration(0,0,15, 0))),
 InsertPeriod = Table.AddColumn(NextQuarterHour, "Period of Day", 
 each if [Hour Number] >= 0 and [Hour Number] < 4 then "After Midnight" else 
 if [Hour Number] >= 4 and [Hour Number] < 8 then "Early Morning" else
 if [Hour Number] >= 8 and [Hour Number] < 12 then "Late Morning" else
 if [Hour Number] >= 12 and [Hour Number] < 16 then "Afternoon" else
 if [Hour Number] >= 16 and [Hour Number] < 20 then "Evening" else "Late Night"),
 InsertPeriodSort = Table.AddColumn(InsertPeriod, "PeriodSort", each 
 if [Hour Number] >= 0 and [Hour Number] < 4 then 0 else 
 if [Hour Number] >= 4 and [Hour Number] < 8 then 1 else
 if [Hour Number] >= 8 and [Hour Number] < 12 then 2 else
 if [Hour Number] >= 12 and [Hour Number] < 16 then 3 else
 if [Hour Number] >= 16 and [Hour Number] < 20 then 4 else 5),
 InsertTimeKey = Table.AddColumn(InsertPeriodSort, "TimeKey", each Time.ToText([Time], "HHmm"), type text)
 in
 InsertTimeKey
in
 CreateTimeTable 

Reference

https://ginameronek.com/2014/10/01/its-just-a-matter-of-time-power-bi-date-time-dimension-toolkit/

Regards,

Charlie Liao

[mmace1]

Thanks, that code seems to "just" make a time table though, so just 24 hours, broken up by minutes.  When what I want is a table taht shows things from the date of <X> to the present, but instead of being dividied by days, is divided by hours (or minutes).

That said - the link goes to a pretty lengthy blog post, do you know if what I'm describing is on that page?