I know how to create calender table, but how do I make one that includes hours?

[mmace1]

Hi,

So, I can create a calender table with the following formula:

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

That one starts at 1-1-2006, as just in my situation, I don't need any dates prior to that.

I use that calender table (with many columns added in), to calculate the number of working days between two events.  The formula I use is

Working Days Between = CALCULATE(SUM('Calender Table'[Workday]),datesbetween('Calender Table'[Date],'Completed Emails'[DateTimeReceived],'Completed Emails'[To Do Ordinal PST]))-1

Just as one example. Works great. 

But, what about number of working hours between two events?  Say in the above example, if something is finished in 2 hours, my result is just 0 (took 0 days).  I'd like to be more precies.

I'm thinking I'd just recreate my calender table, just this time have it progress by each hour, instead of each day.  But, how do I do that...? 

[mmace1]

I found a solution that worked for me here:  It doesn't answer my original question, but in a different way, solves the problem I had (how to calcluate the working hours between two events)

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

[Greg_Deckler]

You may find such a calendar table out there but not sure if that would be a good idea. I would instead modify your formual so that you test if the result is 0 days and then calculate your hours differently in that case.

[mmace1]

Why do you think such a calender table would be a bad idea?

It seems a little odd to me to calculate by portion of a day, unless it's > 1 days, then switch to a less precise daily measurement - but, that is a really good idea that does work.  I went ahead and updated it that way for now using an IF statement.

[Greg_Deckler]

Mainly because I haven't had enough time to think it through and see if that would be useful or not. In general, that's not how it is done though and I believe the reasoning would fall along the fact that it would be really large and the issue with matching/relationships.

In general, there is a key that calendar tables use like:

For July 2017:

7/1/2017

7/2/2017

7/3/2017

...

Obviously this matches up with the date from your fact table.

But, if you had hours, you would have something like:

7/1/2017 00:00:00

7/1/2017 01:00:00

7/1/2017 02:00:00

...

Now the issue I see in that is that you are generally never going to have a time that is exactly spot on the hour. There will almost always be minutes and seconds involved. So, the keys will never match. Not that that isn't solvable perhaps, but I question where it ends at that point.

[mmace1]

OK, to me I'd just:

Create this new calender table that goes by whole hours (if possible).  For my purposes, that would be exact enough.  That would make a calender table 24x longer than any equivalent "normal" calender table, but that's still not that long.

Add a second column that just extracts the date from the date/time column.  So say

Column A

Time in 1 hour Increments

Column B

=DATE(year(Column_A),month(Column_A), DAY(Column_A))

So then Column B can act as a "regular" calendar table for all other purposes. 

For my key on my data table, I'd add a new column that extracts just the time to the nearest hour MROUND(SourceDate, "1:00"), and connect that to Column A on the calender table.

For me - it'd end at that point (to the nearest hour), as that'd be close enough. 

I don't see why one could go by the minute though - now we're up to a table 1440x larger than a "regular" calender table.  For a 10 year table, that's a little over 5 million lines, but - that's nothing for DAX....

Anyway, as I said - thanks!  Jusing an IF statement (duh) definitly gets me closer (possibly close enough), and was a very easy change. 

[KHorseman]

When you create a relationship between two tables, the values have to match exactly. If you have a record with a datetime of 7/17/2017 4:15:36 that will not match any row in your date/time table. You would have to have a row for every second of every day.

[mmace1]

If I were doing it down to the second, then yes I'd need a calender table with every second.

I would think though, that if one only wanted things to the nearest hour - then one only needs a calander table whole hours (so 'only' 24x as long as a standard calander table).

Then to make the match - add a column to the source data table, that converts the time to the nearest hour.  So say [sourcetime], add a column:  Nearest Hour = MROUND(sourcetime,"1:00").  That "Nearest Hour" column should match with this hypothetical calender table that includes hours.