topic Re: Run Hours on Selected date in DAX Commands and Tips

Run Hours on Selected date

Navaneetharaju_ — Mon, 04 Mar 2024 06:43:43 GMT

Hi All,

I need a community help to guide me on this.

Task_name - Task x

Start_date : 01-Mar-2024

Start_time : 01:00:00 PM

Repeat_by - Hours

Repeat_every - 1

repeat_every 1 means from the start_date and start_time it should run the every 1 hours.

If i select 01-Mar-2024 in date slicer, it should show the runs in time_bucket.

Time_slot	Count
1:00:00 PM	1
2:00:00 PM	1
3:00:00 PM	1
4:00:00 PM	1
5:00:00 PM	1
6:00:00 PM	1
7:00:00 PM	1
8:00:00 PM	1
9:00:00 PM	1
10:00:00 PM	1
11:00:00 PM	1

if i select 02-Mar-2024 or upcoming days, i want a result should be like this

Time_slot	Count
12:00:00 AM	1
1:00:00 AM	1
2:00:00 AM	1
3:00:00 AM	1
4:00:00 AM	1
5:00:00 AM	1
6:00:00 AM	1
7:00:00 AM	1
8:00:00 AM	1
9:00:00 AM	1
10:00:00 AM	1
11:00:00 AM	1
12:00:00 PM	1
1:00:00 PM	1
2:00:00 PM	1
3:00:00 PM	1
4:00:00 PM	1
5:00:00 PM	1
6:00:00 PM	1
7:00:00 PM	1
8:00:00 PM	1
9:00:00 PM	1
10:00:00 PM	1
11:00:00 PM	1

I want to display the run count of tasks
Kindly provide the measure or calculated column to acheive this

Re: Run Hours on Selected date

lbendlin — Mon, 04 Mar 2024 18:30:35 GMT

Do you have an end date/time for each task or are they running in perpetuity?

Re: Run Hours on Selected date

Navaneetharaju_ — Mon, 04 Mar 2024 18:45:30 GMT

Hi lbendlin ,

Few tasks have end_date and end_time 26-dec-2024 12:00:00 AM (based on business needs), and few tasks are never ending. End_date and and end time is null.

Re: Run Hours on Selected date

lbendlin — Mon, 04 Mar 2024 18:49:13 GMT

fair enough. Please provide sample data that fully covers your issue. Multiple tasks, some overlapping some not etc. Various schedules.

Re: Run Hours on Selected date

Navaneetharaju_ — Mon, 04 Mar 2024 19:07:27 GMT

Hi lbendlin ,

I have provided the tasks details for repeat_by = hours

task_name	repeat_by	repeat_every	start_date	start_time	End_Date	End_Time
TASK X	hours	1	20-02-24	7:30:00 PM	null	null
TASK Y	hours	1	08-02-24	3:15:00 PM	22-05-24	5:00:00 PM
TASK Z	hours	6	08-02-24	8:41:31 AM	12-03-24	9:15:00 AM
TASK A	hours	1	27-08-23	11:43:58 AM	null	null

Kindly provide a measure or calculated table or column to acheive this.

Re: Run Hours on Selected date

lbendlin — Mon, 04 Mar 2024 19:19:43 GMT

Your start time is too granular. It would be better to agree to start times at the top of the hour only to avoid having to go down to minute or second level granularity (for example second level accuracy would require 86400 rows per day).

Do you have other repeat_by values too?

Re: Run Hours on Selected date

Navaneetharaju_ — Mon, 04 Mar 2024 19:45:51 GMT

I have a 2000 tasks in my table that have a repeat_by - days, weeks, months, years, for those items i have created measure to find the next rundates, it can acheived easily.

RunCount =

VAR x= SELECTEDVALUE('Date'[Date],TODAY())

return

SWITCH(MAX(tasks[repeat_by]),

"days" ,IF(MOD(DATEDIFF(MAX(tasks[utc_start_date]),SELECTEDVALUE('Date'[Date]),DAY),MAX(tasks[repeat_every]))=0 && (SELECTEDVALUE('Date'[Date])<= MAX(tasks[Ends_End_Date]) || MAX(Tasks[Ends_End_Date])=BLANK()) , 1),

"weeks", IF(MOD(DATEDIFF(MAX(tasks[utc_start_date]),SELECTEDVALUE('Date'[Date]),DAY),MAX(tasks[repeat_every])*7)=0 && (SELECTEDVALUE('Date'[Date])<= MAX(tasks[Ends_End_Date]) || MAX(Tasks[Ends_End_Date])=BLANK()), 1),

"months", IF(MOD(DATEDIFF(MAX(tasks[utc_start_date]),SELECTEDVALUE('Date'[Date]),DAY),MAX(tasks[repeat_every])*30)=0 && (SELECTEDVALUE('Date'[Date])<= MAX(tasks[Ends_End_Date]) || MAX(Tasks[Ends_End_Date])=BLANK()), 1),

"Years", IF(MOD(DATEDIFF(MAX(tasks[utc_start_date]),SELECTEDVALUE('Date'[Date]),DAY),MAX(tasks[repeat_every])*365)=0 , 1)

)

with this measure i have found the next_run_dates of the measure these task only runs one time in a day only on the start_time. but hours tasks may run multiple times in a day alone.

task_name	repeat_by	repeat_every	start_date	start_time	End_Date	End_Time
TASK X	hours	1	20-02-24	7:30:00 PM	null	null
TASK Y	hours	1	08-02-24	3:15:00 PM	22-05-24	5:00:00 PM
TASK Z	hours	6	08-02-24	8:41:31 AM	12-03-24	9:15:00 AM
TASK A	hours	1	27-08-23	11:43:58 AM	null	null
TASK B	days	1	01-01-24	12:00:00 AM	26-12-24	12:00:AM
TASK C	weeks	2	01-01-24	3:00:00 AM	null	null
TASK D	months	1	01-01-24	4:00:00 AM	null	null
TASK E	years	1	01-01-24	5:00:00 AM	null	null

i have provided the above measure, how i have found the next_run_dates of the tasks.

please help me to get this done for hours logic.

Re: Run Hours on Selected date

lbendlin — Wed, 06 Mar 2024 00:47:02 GMT

That measure includes a couple of fallacies. Not all months have 30 days, and not all years have 365 days. What should your schedule be like that starts on Jan 31 and repeats every month?

Re: Run Hours on Selected date

Navaneetharaju_ — Wed, 06 Mar 2024 05:39:33 GMT

Hi lbendlin ,

As of now, we could find the next iteration date based on that data they provided. for month is 30 days intervel and year is 365 days intervel, we don't have the majority of tasks in months and years intervel. only few are running, these one or two counts not much impacted in servers.

Hours and minutes tasks are creating more impact on the server, because one task could run many times in a day.