Forum Discussion
Helper IIRun Hours on Selected date
Helper IIHi 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.
Super Userfair enough. Please provide sample data that fully covers your issue. Multiple tasks, some overlapping some not etc. Various schedules.
- Navaneetharaju_
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.
- lbendlin
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_
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())returnSWITCH(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.