Creating a dynamic task list for recurring reports in Power Query (Power BI)

[rusty19]

I am trying to create a recurring task calendar in Power Query (Power BI) for the different reports that our group has to run.  Here is what I have:

Date table including working and non-working days

I also already have an Invoke Function that counts end dates based on start date and duration (taking under consideration working days) that can be utilized

Reports table that looks something like this:

Report Name	Frequency	Acronym
Report One	Monthly	RONE
Report Two	Monthly	RTWO
Report Three	Quarterly	RTHREE

Tasking lists for each report (for example):

Report Name	Timing	taskID	taskNAME	StartDATE	Duration (WD)	EndDATE
Report One	Dec2025	RONE01	Send tasking email	20th of previous month	0
Report One	Dec2025	RONE02	Create content	20th of previous month		15th of report month
Report One	Dec2025	RONE03	Send reminder	RONE02 EndDATE minus 2WD	0
Report One	Dec2025	RONE04	Review	RONE02 EndDATE	4
Report One	Dec2025	RONE05	Submit for approval	RONE04 EndDATE	2

(if columns need to be changed, I can be flexible)

WD = Working Days

Explanation on start and end dates and duration:

• For the December Report the StartDATE of taskID RONE01 would be November 20th (20th of previous month)
• For taskID RONE02 the StartDATE is the same November 20th and the EndDATE would be December 15th (15th of report month)
• For taskID RONE03 the StartDATE would be based on the end date of the previous task minus 2 working days.
• and so on…

Things to consider:

• Start dates and end dates must be actual working days
• Duration is always counting working days

With this information I am trying to create a dynamic calendar, i.e., which tasks for which report is due when etc. based on their frequency.

I had gotten some information from Master Semicolon (a.k.a. Copilot) but they have recently changed some settings at work which eliminated our ‘Agent’ feature and I have lost most of the back and forth already done.

So looking for help from live people please.  The part I am having issues with is getting all the parts to work together.  Once that is don, building the Power BI reports and automation, etc. I can do.

[MarkLaf]

I want to home in on what I think is the main complexity of your request and for which other specialized PM/task software is typically used.

It's not calculating end dates from start+duration. It's not handling your natural language column (e.g., "20th of previous month" would need to be converted upstream into Month Offset, Day Offset columns, which are then easy inputs for calculating a date).

It's the dependencies between your tasks. While it's technically possible in Power Query, it would be much simpler to input all the tasks+dependencies into some other software, have that calculate all start/end WD, then export your task list for visualization in Power BI.

That said, it would be an interesting problem to tackle. However, I'm still unsure on what is your desired output. Do you basically want something like the below, where the Start WD and End WD are calculated for all recurrences up to some end date or duration (which you have not specified btw, unless you want this paramaterized or something)?

Report Name	Timing	taskID	taskNAME	StartDATE	Duration (WD)	EndDATE	Start WD	End WD
Report One	Dec2025	RONE01	Send tasking email	20th of previous month	0		11/20/2025	11/20/2025
Report One	Dec2025	RONE02	Create content	20th of previous month		15th of report month	11/20/2025	12/15/2025
Report One	Dec2025	RONE03	Send reminder	RONE02 EndDATE minus 2WD	0		12/11/2025	12/11/2025
Report One	Dec2025	RONE04	Review	RONE02 EndDATE	4		12/15/2025	12/19/2025
Report One	Dec2025	RONE05	Submit for approval	RONE04 EndDATE	2		12/19/2025	12/23/2025
Report One	Jan2026	RONE01	Send tasking email	20th of previous month	0		12/22/2025	12/22/2025
Report One	Jan2026	RONE02	Create content	20th of previous month		15th of report month	12/22/2025	1/15/2025
Report One	Jan2026	RONE03	Send reminder	RONE02 EndDATE minus 2WD	0		1/13/2025	1/13/2025
Report One	Jan2026	RONE04	Review	RONE02 EndDATE	4		1/15/2025	1/21/2025
Report One	Jan2026	RONE05	Submit for approval	RONE04 EndDATE	2		1/21/2025	1/23/2025
...	...	...	...	...	...	...	...	...

Note: assume next WD for specified dates when they are non-WD (e.g. 12/20/2025)

[lbendlin]

I am trying to create a recurring task calendar in Power Query (Power BI) for the different reports that our group has to run. 

Power BI is a reporting tool, not a task scheduler. Use the apps that were designed for that (Task Scheduler, Outlook etc).

[rusty19]

I understand your comment, but the Power BI part of this is only the end game for displaying the schedule per report and creating a reports calendar.

I will be using Power Automate to display taskings in the proper MS tools.

[v-saisrao-msft]

Hi @rusty19,

As you mentioned, using Power Automate for task orchestration and Power BI only for displaying the resulting schedule and calendar view is the right separation of responsibilities. Could you please confirm whether this approach has resolved your issue?

Thank you.

[rusty19]

Hello,

sorry for the delay.  Not this does not resulve the issue.  I have the beginning (the data and different tables) and the end (Power BI reports) but I am missing the middle - how to 'expand/merge' all the information together.  I think there is a begining of an answer in this chat.

Thx.

[v-saisrao-msft]

Hi @rusty19,

Set up a working-day date table and use rules instead of fixed dates to define reports and task templates. Generate report periods, expand tasks over those periods, and group them by Report and Period. For each group, calculate task dates in order, considering working-day logic and task dependencies. This approach results in a dynamic task calendar that is reliable and refresh-safe.

List.Accumulate - PowerQuery M | Microsoft Learn

How to GROUP BY or summarize rows - Power Query | Microsoft Learn

What is Power Query? - Power Query | Microsoft Learn

Thank you.

[rusty19]

Hello,

I feel like this is going in the right direction, however I am having a hard time with the important part which is creating the recurrences, etc.

In your reply above you say:

"Set up a working-day date table and use rules instead of fixed dates to define reports and task templates..." I can confirm that I have this.  Then you say:

"... Generate report periods, expand tasks over those periods, and group them by Report and Period..." this is the part I need help with.  I have looked at 2 of the links you provided, but I am not sure I understand how to use them for my situation.

Is it possible to get more details.  Let me know if you need more info.

thx!