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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.