Counting items within a timeframe

[monica2563]

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

[Anonymous · Accepted solution]

Hi, @monica2563 

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.

[monica2563]

thank you for your help !!

[ryan_mayu]

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?