Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter time is in the time range

Hi everyone,

I have a time column with the format "dd-mm-YY hh: mm: ss". I need to filter and count the number of hours and minutes records that are between 23:00 - 6:00 (next day) and the number of hours and minutes records that are between 6:00 - 23:00.

For example:

Please help me.

Need Help

7 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Can you please give a little more info so we can help? Do you have a DimDate table? I assume you're wanting to get this count for every date? I'm initially thinking you could use a COUNTROWS and FILTER. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi AllisonKennedy ,

      My job is to calculate the data in 2 periods between 6:00 - 23:00 and 23:00 - 6:00. I used COUNTROW and FILTER to calculate, but I do not know how to classify the data by 2 time periods: 6:00 - 23:00 and 23:00 - 6:00.

      This is the time column of my data table:

       

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        I agree it is a good practice to split Dates and Times.  Either way though, a DAX expression like this will get you your desired count.  I put your data in a table called Times and called the column DateTime.

         

        Count 6 to 23 = COUNTROWS(Filter(Times, AND(Hour(Times[DateTime])>=6, HOUR(Times[DateTime])<23)))
         
        For the other one, just change it to >=23 and <6
         

        If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

        Regards,

        Pat

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    6 years ago

    you need to split your date and time columns so that you can do that more effectively.

     

    see here for more information

    https://www.exceljetconsult.com.ng/home/blog/power-query-split-date-and-time-into-separate-columns/

    you can then create a time lookup table 

    https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

     

    then you can create a calculated measure on the time column or use the time table for slicing and dicing.