Custom Date Parameters

[Anonymous]

Hi, I have a report with calculations based on the typical calendar day. However, I've been tasked with reporting my calculations to overlap to 2 calendar days i.e. between 5pm (previous day) to 5pm (next day).

How would I approach this since all my calculations are linked to my calendar table? I know the question maybe vague so here's an example:

I have a measure that calculates the total amount of job bookings in a day and they range from 12 midnight to 11.59pm. Now I need to calculate total job bookings between 5pm (previous day) to 5pm (next day). Hope that makes sense.

Appreciate any suggestions. Thanks. 

[kentyler]

You might consider that you need a calendar table with the granularity of hours, rather than the normal days. Then on each row you put a new "day" value that corresponds to your 5:00 to 5:00 day.

Then if you have records with date/time values hooked up to a date/time field in your calendar table..you can query for the records in a "day", meaning your offset row, and you can count the number of records in that "offset" "day"

[Anonymous]

@kentyler Thank you for responding. Do you have a sample calendar table I can reference?

[kentyler]

https://www.youtube.com/watch?v=BtYn1hfdSAM

[Anonymous]

Hi @kentyler I was hoping for sample calendar table with granularity in hours as you suggested...

[kentyler]

sorry, I would build something like that in Access or Excel, but I don't have one already to hand

[Anonymous]

I'm also building this in Excel PowerPivot so was hoping if you could provide a sample date table with hours and some sample mock data to show how to calculate the offsets and integrate/link with my existing date table? If not possible, then no problem I'll reach out to other members in the forum. Thanks. 

[kentyler]

sorry, that's a lot of work for a forum like this

[kentyler]

This morning I had a new idea. Instead of listing every hour of every day in your calendar, you could create a separate table that had one row for each hour in the day. That row would include a field with an offset value...the value you need to add to the day for that particular hour. Up til 5 the offset would be 0, 5 and after it would be 1.

Then you can calculate the "business day" in any measure or calculated column by using DATEADD() to add the offset to the "calendar day".

This would mean you split your date time into a "date" and "hour" column, so you could link the hour column to your lookup table for hours.

[Anonymous]

Thanks but am more of a visual person to help me understand, perhaps you could provide screenshot of a sample date table with your suggestion?

[kentyler]

here is a sample "hour" table

Hour	Day Offset
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	0
13	0
14	0
15	0
16	0
17	1
18	1
19	1
20	1
21	1
22	1
23	1
24	1