Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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:

1.JPG

Please help me.

1 ACCEPTED SOLUTION
v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

Let me know if you'd like to get this one:

2.PNG

Pbix attached.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

7 REPLIES 7
v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

Let me know if you'd like to get this one:

2.PNG

Pbix attached.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
vanessafvg
Super User
Super User

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.

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




AllisonKennedy
Super User
Super User

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. 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

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:

1.JPG

 

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Hi @mahoneypat ,

23:00 - 6:00 there is a time period of the next day, it will be incorrect when the time is from 0:00 - 6:00, hour <6 but not >= 23

From your first picture, it wasn't clear that you were using the Date portion of the DateTime.  You could do that with DAX too, but a better approach would be to split your DateTime into two columns in query, create a Date table, and make a relationship on Date.  If you want 0-6 hrs to count on the previous date, in query you could use a custom column to subtract 1 day from the date if the hour <=6 (and don't subtract if 6-23 hr).

 

Does that work?

 

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

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors