Forum Discussion

tenfingers's avatar
tenfingers
Icon for Advocate II rankAdvocate II
6 years ago
Solved

Slicing a time range across two dates

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
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

     

    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 

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

     

    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's avatar
      tenfingers
      Icon for Advocate II rankAdvocate II
      6 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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.