Power Platform Community Forum Thread Details

Basic scenario, we have a list of dates related to month end close and forecasting/reporting (month close, forecast sheets sent out, forecasts due, etc) that exist in an excel sheet (if there is a better spot for them that's fine) in a sharepoint folder. The general purpose of the flow would be to run each month and:

1) Find the previous months completed forecast spreadsheet file (May_2023_Forecast.xlsx)

2) Make a copy of this forecast spreadsheet and rename it to update for the new month (from May_2023_Forecast.xlsx to June_2023_Forecast.xlsx)

3) Update the dates to the current month's cycle in the new excel file

4) Send a link to the new file to our project managers to update their forecasts

Because month end dates vary slightly based on the month and the # of weeks in that month, I don't really want to run this "every 4th Saturday 1am" for example, but rather by referencing a specific list/calendar of dates we setup manually and may adjust from time to time to fall outside of what could otherwise be controlled directly by programmed logic. For example if the close was to be the last Friday of the month but that happens to be Christmas, then that month it would be the last thursday (or following monday, just depends).

I'm looking for a way to get the flow setup to reference the list of dates that the accounting team can update so that the automations happen based on the dates they list out. If there is a better way to store those dates and update them in the future (sharepoint list, outlook calendar events, etc) I'm definitely open to that. I'm sure it must be possible, but not certain where to start to get the flow to essentially watch the excel file and if a date in that file becomes "TODAY()" then the flow triggers. It would then need to reference that file to know it is triggering for say May 2023 close which happens end of may early june, which means look for the April 2023 forecast sheet and make a copy of it renaming it to May 2023. We could definitely do it off a manual trigger to but I'm trying to take people 100% out of the equation and just have accounting keep their list of dates current.

I think an approach would be triggering the flow(s) off calendar events, but then I fall flat on how to make the flow see each new or updated calendar event after the first one.

Categories:

Building flows