Forum Discussion

Navaneetharaju_'s avatar
Navaneetharaju_
Icon for Helper II rankHelper II
2 years ago

Run Hours on Selected date

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 ...
"Show and tell"
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

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?

Navaneetharaju_'s avatar
Navaneetharaju_
Icon for Helper II rankHelper II
2 years ago

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_namerepeat_byrepeat_everystart_datestart_timeEnd_DateEnd_Time
TASK Xhours120-02-247:30:00 PMnullnull
TASK Yhours108-02-243:15:00 PM22-05-245:00:00 PM
TASK Zhours608-02-248:41:31 AM12-03-249:15:00 AM
TASK Ahours127-08-2311:43:58 AMnullnull
TASK Bdays101-01-2412:00:00 AM26-12-2412:00:AM
TASK Cweeks201-01-243:00:00 AMnullnull
TASK Dmonths101-01-244:00:00 AMnullnull
TASK Eyears101-01-245:00:00 AMnullnull


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.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    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?

    • Navaneetharaju_'s avatar
      Navaneetharaju_
      Icon for Helper II rankHelper II
      2 years ago

      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.