Forum Discussion
Counting items within a timeframe
Hello, I pulling data as an admin on datasets, when and what time they are refreshed. I am trying to count the number of datasets being refreshed every 30 min from 6AM to 10AM daily. I have a column for dataset name, date it was refreshed and start and end time for each one. Again, trying to determin number of items being refreshed every 30 min.
| Dataset | Date | Start | End |
store report | 5/05/24 | 1:47:47PM | |
| daily sales report | 5/05/24 | 8:46:46AM | |
| FIeld POI | 5/05/24 | 12:00:49 AM | 12:02:59 AM |
Hi, Anonymous
Based on your description, I created the following sample data:
You'll need to create a schedule like this:
This schedule consists of 30-minute time slots from 6:00 to 10:00. You can use the following DAX expression to figure out which items are successfully refreshed every 30 minutes:
Refresh Count = VAR _seleted = SELECTEDVALUE(Table3[time]) VAR _start_time = FORMAT(TIMEVALUE(LEFT(_seleted,11)),"hh:mm:ss") VAR _end_time = FORMAT(RIGHT(_seleted,11),"hh:mm:ss") RETURN CALCULATE(COUNTROWS('Table'), FILTER('Table', FORMAT('Table'[Start Time],"hh:mm:ss")>=_start_time&& FORMAT('Table'[End Time],"hh:mm:ss")<=_end_time ) )The results are as follows:
The above is a reference, and you can adjust it appropriately according to your actual situation. I've provided the PBIX file used this time below.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
could you pls provide more data? based on the sample data, we can't see any dataset refreshed every 30 mins. What about refreshed every 29 or 31 mins? Could you pls explain more about your request?
Hi, Anonymous
Based on your description, I created the following sample data:
You'll need to create a schedule like this:
This schedule consists of 30-minute time slots from 6:00 to 10:00. You can use the following DAX expression to figure out which items are successfully refreshed every 30 minutes:
Refresh Count = VAR _seleted = SELECTEDVALUE(Table3[time]) VAR _start_time = FORMAT(TIMEVALUE(LEFT(_seleted,11)),"hh:mm:ss") VAR _end_time = FORMAT(RIGHT(_seleted,11),"hh:mm:ss") RETURN CALCULATE(COUNTROWS('Table'), FILTER('Table', FORMAT('Table'[Start Time],"hh:mm:ss")>=_start_time&& FORMAT('Table'[End Time],"hh:mm:ss")<=_end_time ) )The results are as follows:
The above is a reference, and you can adjust it appropriately according to your actual situation. I've provided the PBIX file used this time below.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
