Slicing a time range across two dates

[tenfingers]

I have a report where I need to slice the business hours which are 6am to 1am the following day. I only need to look at one business period at a time. I currently have a seperate date table and a time table. Looking for some ideas on how I can slice certain times one day and different times the following day or any suggestions on dealing with a time period that spans to different dates.
Cheers

[Greg_Deckler]

Can you provide some sample data and expected output? 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

[tenfingers]

Thanks for the replies.

Model is simple date and time table connected to sales facts and and another shift fact table:

I guess I am looking for a modelling approach that will let me filter a report page to be 6am to 1am and allow me to use these times on the axis of a line chart:

Cheers

[Greg_Deckler]

Right @tenfingers  but I am looking to not have to type a bunch of data that may or may not be representative of your actual data formats in order to test out a solution. 

[tenfingers]

Thanks Greg,

I am using a date table with the datekey column connecting to my fact tables:

This is my Time Table where time_id connects to the fact table:

This is my sales fact with the timeid that connects the time table:

Here is the rest of sales fact table with date id that connects the date table:

I have a measure that sums sales from the sales fact table and would like to plot this on a line chart with hours 6am to 1am on the X axis. I would also like to be able to filter report pages to this range. Let me know if you need any more details.

Thanks in advance.

[tenfingers]

Slightly embarrassing, I think there’s a very simple solution: a calculated column with a conditional statement on the date and time in the fact table should do the trick to filter all sales in the required time period. I was so hung up on using my date and time tables to filter that I couldn’t see simple solution. Thanks anyway for taking the time to look at this.
Cheers

[Greg_Deckler]

Glad you found a solution @tenfingers . Nothing to be embarrassed about. Sometimes stepping away from the problem for a bit and approaching it again with a fresh set of eyes is the way to go!

[amitchandak · Accepted solution]

I am hoping you have a timestamp column. Or the date and time stamp column. Based on that have these two columns

Bucket Date = if(hour([Timestamp])<=1, [Timestamp].date-1, [Timestamp].date)
Bucket = switch(true(),
hour([Timestamp])<=1 || hour([Timestamp])>=18 , "6PM to 1AM",
/// Othe bucket logic
)

@tenfingers 

[tenfingers]

Thanks @amitchandak, this is the logic I used in the end.

Cheers