Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Start Time Filter

Hello,

 

Need some help on the following.

So I have a table on my powerBI (see below, just an example).

 

Time StampHourLPN
11/11/19 7:04 AM7LPN1
11/11/19 6:44 AM6LPN2
11/11/19 6:51 AM6LPN3
11/11/19 8:01 AM8LPN4
11/11/19 7:07 AM7LPN5
11/11/19 8:56 AM8LPN6
11/11/19 9:14 AM9LPN7
11/11/19 8:04 AM8LPN8
11/11/19 8:14 AM8LPN9
11/11/19 9:47 PM21LPN10
11/11/19 11:22 PM23LPN11
11/11/19 11:58 PM23LPN12
11/12/19 12:43 AM0LPN13
11/12/19 2:03 AM2LPN14
11/12/19 2:42 AM2LPN15
11/12/19 2:43 AM2LPN16
11/12/19 2:49 AM2LPN17
11/12/19 3:41 AM3LPN18
11/12/19 3:43 AM3LPN19
11/12/19 4:39 AM4LPN20
11/12/19 4:26 AM4LPN21
11/12/19 5:38 AM5LPN22
11/12/19 5:40 AM5LPN23
11/12/19 4:56 AM4LPN24

 

the challenge that I have is I want that if the user choose specific date on my Date Slicer.(I put a column with DateValue formula)

The data should start from 9PM from the previous day upto 8:59PM current date(which the user chosen)

 

Example would be is, if I or the user choose 12/18/19 on my slicer.

it will show Hour on my graph from 12/17 hour 21,22,23 and 12/18 hour 1 to 20.

hope it makes sense.

 

*so if you look at the graph below.(real data, not the table above)

it should show 12/17 9PM(hour 21) up to 12/18 8:59PM(hour20) (12/18 is the chosen filter on the slicer)

 

Appreciate any help. thanks!

 

 

 

 

Need Help
  • Thim's avatar
    Thim
    6 years ago

    I am guessing you are on a different version of Power BI than i am.

     

    Try and change all the ; to ,

     

    I beleive (don't quote me) that ; is the syntax on Dax formula that is used in EU, and , is used in US.

4 Replies

  • Thim's avatar
    Thim
    Resolver V
    6 years ago

    Hey there.

     

    Big help you already have the Hour seperated 🙂

     

    I would make a formula like this

    Work Date = IF('Table (2)'[Hour]>20;
    DATE(YEAR('Table (2)'[Time Stamp]);MONTH('Table (2)'[Time Stamp]);DAY('Table (2)'[Time Stamp])+1);
    DATE(YEAR('Table (2)'[Time Stamp]);MONTH('Table (2)'[Time Stamp]);DAY('Table (2)'[Time Stamp]))
    )
    Essentially you make a new Date column that adds a day if your Hour column has a number greater than 20.
     

     

    Hope this will help. 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello,

       

      I got an error when I tried your formula. Did I forgot something?

       

       

      Thanks

      • Thim's avatar
        Thim
        Resolver V
        6 years ago

        I am guessing you are on a different version of Power BI than i am.

         

        Try and change all the ; to ,

         

        I beleive (don't quote me) that ; is the syntax on Dax formula that is used in EU, and , is used in US.